1 00:00:00,760 --> 00:00:03,250 Currently, our full catalog array function, 2 00:00:03,250 --> 00:00:07,390 returns an array with title, category and image. 3 00:00:07,390 --> 00:00:09,850 When we're on the details page, we need to pull 4 00:00:09,850 --> 00:00:15,190 many more details about this specific item including information from other tables. 5 00:00:15,190 --> 00:00:18,420 To do this, we're going to use a join, which is a method for 6 00:00:18,420 --> 00:00:22,850 combining or joining together data from multiple tables. 7 00:00:22,850 --> 00:00:23,860 Let's launch workspaces. 8 00:00:25,570 --> 00:00:29,390 Open the functions.php file within the includes folder. 9 00:00:29,390 --> 00:00:31,913 Start by duplicating the full catalogue array. 10 00:00:34,912 --> 00:00:41,090 Well then rename this function single_item_array. 11 00:00:41,090 --> 00:00:44,790 The first thing we're going to do is add the rest of the columns from our media 12 00:00:44,790 --> 00:00:49,710 table, format and year. 13 00:00:49,710 --> 00:00:52,394 Then we'll space out this query so that it's easier to read. 14 00:01:00,179 --> 00:01:03,700 You don't have to put the whole query string on one line. 15 00:01:03,700 --> 00:01:06,430 Inside the quotation marks, you can press the Enter or 16 00:01:06,430 --> 00:01:09,310 return key to put in a new line. 17 00:01:09,310 --> 00:01:13,520 Those new lines, or hard returns, do get sent to SQL Lite. 18 00:01:13,520 --> 00:01:16,680 But SQL queries can have hard returns in them. 19 00:01:16,680 --> 00:01:18,960 So everything works just fine. 20 00:01:18,960 --> 00:01:23,620 You'll often see longer queries written as strings with multiple lines like this. 21 00:01:23,620 --> 00:01:26,424 Next we're going to add a joint on our genres table. 22 00:01:30,918 --> 00:01:37,960 We'll link Media.genre_id 23 00:01:37,960 --> 00:01:42,050 to .genre_id. 24 00:01:42,050 --> 00:01:44,910 Now we can add the genre field to our select. 25 00:01:44,910 --> 00:01:47,180 Not all media items are books, so 26 00:01:47,180 --> 00:01:50,830 they will not all have details in the books table. 27 00:01:50,830 --> 00:01:52,738 We can use a special OUTER JOIN. 28 00:01:56,080 --> 00:02:00,420 This allows us to select the details from the books table only if they exist. 29 00:02:01,450 --> 00:02:06,840 We'll actually specify this as a left outer join, because the table on the left, 30 00:02:06,840 --> 00:02:09,660 the media table, is the main query table. 31 00:02:09,660 --> 00:02:11,910 And we want to always pull information from there. 32 00:02:13,120 --> 00:02:16,510 We also want to optionally pull data from the books table. 33 00:02:18,120 --> 00:02:22,140 But we only wanna pull the books details if there's data that matches up with our 34 00:02:22,140 --> 00:02:22,684 media ID. 35 00:02:33,500 --> 00:02:37,400 Now we can add the publisher and ISBN to our SELECT statement as well. 36 00:02:43,960 --> 00:02:46,510 Finally, this is a single item array. 37 00:02:46,510 --> 00:02:51,200 So we only want to select a single item, not all the items in the database. 38 00:02:51,200 --> 00:02:54,930 We're going to pass the idea of the item into this function. 39 00:02:54,930 --> 00:02:57,290 So we add the id as an attribute. 40 00:02:59,800 --> 00:03:01,740 We also need to add a WHERE clause. 41 00:03:06,100 --> 00:03:14,490 We only want to select options where the media table media_id equals id. 42 00:03:14,490 --> 00:03:18,087 Since this is a single item, we're going to change our fetch statement. 43 00:03:20,871 --> 00:03:24,490 Instead of using fetchAll, we'll just use fetch. 44 00:03:26,050 --> 00:03:31,104 To test this out let's add a simple call to our function within a var_dump and 45 00:03:31,104 --> 00:03:32,441 pass it the id of 1. 46 00:03:37,470 --> 00:03:39,400 Now we can preview this in the browser. 47 00:03:43,560 --> 00:03:47,650 Any page that includes the functions.php file will show in an array 48 00:03:47,650 --> 00:03:52,050 of our item with the media id of 1, which is the design patterns book. 49 00:03:53,120 --> 00:03:57,400 Before we take the next step and actually use this function in our details page, 50 00:03:57,400 --> 00:04:00,690 we need to talk about another important security concern. 51 00:04:00,690 --> 00:04:01,690 SQL injection.