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