Preview
Querying Multiple Tables with JOIN
4:02JOIN is a method for combining, or joining, together data from multiple tables. We’ll use JOIN to add the extra details we need from our Genre and Books tables. We’ll also explore another fetch method.
Database Diagram
Links
WHAT IS A SQL JOIN?
SQL Basics course
Database Foundations
Example Code
<?php
try {
$results = $db->query(
"SELECT title, category, img, format, year,
publisher, isbn, genre
FROM Media
JOIN Genres ON Media.genre_id=Genres.genre_id
LEFT OUTER JOIN Books
ON Media.media_id = Books.media_id
WHERE Media.media_id = ?"
);
} catch (Exception $e) {
echo "bad query";
echo $e;
}
$item = $results->fetch(PDO::FETCH_ASSOC);
Currently, our full
catalog array function,
0:00
returns an array with title,
category and image.
0:03
When we're on the details page,
we need to pull
0:07
many more details about this specific item
including information from other tables.
0:09
To do this, we're going to use a join,
which is a method for
0:15
combining or joining together
data from multiple tables.
0:18
Let's launch workspaces.
0:22
Open the functions.php file
within the includes folder.
0:25
Start by duplicating
the full catalogue array.
0:29
Well then rename this
function single_item_array.
0:34
The first thing we're going to do is add
the rest of the columns from our media
0:41
table, format and year.
0:44
Then we'll space out this query so
that it's easier to read.
0:49
You don't have to put the whole
query string on one line.
1:00
Inside the quotation marks,
you can press the Enter or
1:03
return key to put in a new line.
1:06
Those new lines, or hard returns,
do get sent to SQL Lite.
1:09
But SQL queries can have
hard returns in them.
1:13
So everything works just fine.
1:16
You'll often see longer queries written
as strings with multiple lines like this.
1:18
Next we're going to add
a joint on our genres table.
1:23
We'll link Media.genre_id
1:30
to .genre_id.
1:37
Now we can add the genre
field to our select.
1:42
Not all media items are books, so
1:44
they will not all have
details in the books table.
1:47
We can use a special OUTER JOIN.
1:50
This allows us to select the details
from the books table only if they exist.
1:56
We'll actually specify this as a left
outer join, because the table on the left,
2:01
the media table, is the main query table.
2:06
And we want to always pull
information from there.
2:09
We also want to optionally pull
data from the books table.
2:13
But we only wanna pull the books details
if there's data that matches up with our
2:18
media ID.
2:22
Now we can add the publisher and
ISBN to our SELECT statement as well.
2:33
Finally, this is a single item array.
2:43
So we only want to select a single item,
not all the items in the database.
2:46
We're going to pass the idea of
the item into this function.
2:51
So we add the id as an attribute.
2:54
We also need to add a WHERE clause.
2:59
We only want to select options where
the media table media_id equals id.
3:06
Since this is a single item,
we're going to change our fetch statement.
3:14
Instead of using fetchAll,
we'll just use fetch.
3:20
To test this out let's add a simple call
to our function within a var_dump and
3:26
pass it the id of 1.
3:31
Now we can preview this in the browser.
3:37
Any page that includes the functions.php
file will show in an array
3:43
of our item with the media id of 1,
which is the design patterns book.
3:47
Before we take the next step and actually
use this function in our details page,
3:53
we need to talk about another
important security concern.
3:57
SQL injection.
4:00