1 00:00:01,250 --> 00:00:08,740 We've successfully moved our media catalog into a database. 2 00:00:08,740 --> 00:00:11,840 All the pages are now working as they did before. 3 00:00:11,840 --> 00:00:15,010 However, we have a couple places where queries are not 4 00:00:15,010 --> 00:00:17,110 as efficient as they could be. 5 00:00:17,110 --> 00:00:20,710 We have a number of functions that are taking the full catalog array 6 00:00:20,710 --> 00:00:24,440 before using PHP to narrow down the items to a smaller list. 7 00:00:25,490 --> 00:00:30,740 Instead, we should modify these functions to use their own queries to retrieve 8 00:00:30,740 --> 00:00:33,300 only the items we need in the first place. 9 00:00:33,300 --> 00:00:37,450 For example, retrieve just the books or just the movies. 10 00:00:38,730 --> 00:00:39,940 As we do this, 11 00:00:39,940 --> 00:00:43,960 we'll look at a number of different ways to use sequel specific features. 12 00:00:43,960 --> 00:00:48,710 Like limits, ordering, and aggregate functions to fine tune our queries. 13 00:00:49,940 --> 00:00:53,650 Let's start with using sequel to grab the four random items for our home page. 14 00:00:54,950 --> 00:00:58,550 >> Launch the workspace associated with this video and open index.php. 15 00:00:58,550 --> 00:01:03,460 We're only displaying four random items on the home page. 16 00:01:03,460 --> 00:01:07,700 But we pulled the entire catalog from the database before using PHP 17 00:01:07,700 --> 00:01:09,750 to select the random items. 18 00:01:09,750 --> 00:01:14,180 Instead let's create a new function that uses a SQL query to pull for 19 00:01:14,180 --> 00:01:16,970 random items directly from the database. 20 00:01:16,970 --> 00:01:20,680 Let's open the functions.php file from within the includes folder. 21 00:01:20,680 --> 00:01:23,374 We'll start by duplicating the full catalog array. 22 00:01:27,114 --> 00:01:29,874 Then we'll rename it random catalog array. 23 00:01:34,634 --> 00:01:38,599 This function is going to work exactly the same way as our full catalog array, 24 00:01:38,599 --> 00:01:41,650 with two small changes to the SQL query. 25 00:01:41,650 --> 00:01:46,740 We include our database connection, query our database, fetch all results, 26 00:01:46,740 --> 00:01:48,940 then return those results back to our function call. 27 00:01:50,060 --> 00:01:52,634 Let's break this query line up by statement types. 28 00:02:00,074 --> 00:02:03,650 This will make it a little easier to read as we add more statements. 29 00:02:03,650 --> 00:02:05,190 We'll start by adding an ORDER BY. 30 00:02:08,220 --> 00:02:12,600 Then we'll use the SQLite function, RANDOM, just like in php 31 00:02:12,600 --> 00:02:16,300 when using a function, make sure you add the opening and closing parenthesis. 32 00:02:17,350 --> 00:02:19,870 Then we'll add a limit and tell it 4. 33 00:02:19,870 --> 00:02:25,000 Now we're only selecting four random items. 34 00:02:25,000 --> 00:02:28,950 If you're following along using a local environment with my sequel, 35 00:02:28,950 --> 00:02:31,380 the function is called random. 36 00:02:31,380 --> 00:02:35,380 If you're using another database, make sure you look up the specific syntax for 37 00:02:35,380 --> 00:02:36,960 your database. 38 00:02:36,960 --> 00:02:40,290 Now we need to use this function on the index page. 39 00:02:40,290 --> 00:02:43,860 We'll save this file and go back into index.php. 40 00:02:43,860 --> 00:02:46,130 We can remove this catalog. 41 00:02:46,130 --> 00:02:49,860 That calls our full catalog array, since we're no longer using our full catalog. 42 00:02:51,040 --> 00:02:54,824 Then we go down to where we called the built in function array_rand. 43 00:02:56,440 --> 00:03:00,420 Will change this to our new random catalog. 44 00:03:02,380 --> 00:03:06,230 We're selecting the information from our database within our new function. 45 00:03:06,230 --> 00:03:08,160 So we don't need to pass any arguments. 46 00:03:09,220 --> 00:03:13,180 If you want to take this as step further, you could pass the number of random items. 47 00:03:13,180 --> 00:03:15,680 Make sure that you use a prepared statement when accepting 48 00:03:15,680 --> 00:03:17,350 outside information. 49 00:03:17,350 --> 00:03:22,080 Our random variable now contains a full array of items, instead of just the ids. 50 00:03:22,080 --> 00:03:25,254 So let's change id to item in this foreach loop. 51 00:03:28,404 --> 00:03:33,697 We've already changed or get_item_html to use the media id instead of the array key, 52 00:03:33,697 --> 00:03:37,930 so let's modify this function to just pass the single variable Item. 53 00:03:41,620 --> 00:03:45,000 We also need to update our function so it only receives one argument. 54 00:03:46,070 --> 00:03:53,840 Go back to functions, and will scroll down to our get item HTML. 55 00:03:53,840 --> 00:03:56,460 We'll remove the ID and just keep the item. 56 00:03:58,130 --> 00:04:01,470 Let's take a quick peek in the browser to make sure our home page is still working. 57 00:04:03,400 --> 00:04:08,560 We refresh and the four random items change, but if we try to go to the catalog 58 00:04:08,560 --> 00:04:14,710 page, we see that our changes to the get HTML function broke our catalog pages. 59 00:04:14,710 --> 00:04:15,808 So let's fix those. 60 00:04:15,808 --> 00:04:19,421 Open catalog.php. 61 00:04:19,421 --> 00:04:21,090 Scroll down. 62 00:04:21,090 --> 00:04:25,510 And where we're using the get_item_html, 63 00:04:25,510 --> 00:04:28,210 we're going to pass just the item and not the ID. 64 00:04:29,930 --> 00:04:33,230 Now we go back to our browser and refresh our page. 65 00:04:34,780 --> 00:04:36,780 Great, our catalog pages are working again.