Getting Specific with Queries

We've successfully moved our media catalog into a database. 0:01 All the pages are now working as they did before. 0:08 However, we have a couple places where queries are not 0:11 as efficient as they could be. 0:15 We have a number of functions that are taking the full catalog array 0:17 before using PHP to narrow down the items to a smaller list. 0:20 Instead, we should modify these functions to use their own queries to retrieve 0:25 only the items we need in the first place. 0:30 For example, retrieve just the books or just the movies. 0:33 As we do this, 0:38 we'll look at a number of different ways to use sequel specific features. 0:39 Like limits, ordering, and aggregate functions to fine tune our queries. 0:43 Let's start with using sequel to grab the four random items for our home page. 0:49 >> Launch the workspace associated with this video and open index.php. 0:54 We're only displaying four random items on the home page. 0:58 But we pulled the entire catalog from the database before using PHP 1:03 to select the random items. 1:07 Instead let's create a new function that uses a SQL query to pull for 1:09 random items directly from the database. 1:14 Let's open the functions.php file from within the includes folder. 1:16 We'll start by duplicating the full catalog array. 1:20 Then we'll rename it random catalog array. 1:27 This function is going to work exactly the same way as our full catalog array, 1:34 with two small changes to the SQL query. 1:38 We include our database connection, query our database, fetch all results, 1:41 then return those results back to our function call. 1:46 Let's break this query line up by statement types. 1:50 This will make it a little easier to read as we add more statements. 2:00 We'll start by adding an ORDER BY. 2:03 Then we'll use the SQLite function, RANDOM, just like in php 2:08 when using a function, make sure you add the opening and closing parenthesis. 2:12 Then we'll add a limit and tell it 4. 2:17 Now we're only selecting four random items. 2:19 If you're following along using a local environment with my sequel, 2:25 the function is called random. 2:28 If you're using another database, make sure you look up the specific syntax for 2:31 your database. 2:35 Now we need to use this function on the index page. 2:36 We'll save this file and go back into index.php. 2:40 We can remove this catalog. 2:43 That calls our full catalog array, since we're no longer using our full catalog. 2:46 Then we go down to where we called the built in function array_rand. 2:51 Will change this to our new random catalog. 2:56 We're selecting the information from our database within our new function. 3:02 So we don't need to pass any arguments. 3:06 If you want to take this as step further, you could pass the number of random items. 3:09 Make sure that you use a prepared statement when accepting 3:13 outside information. 3:15 Our random variable now contains a full array of items, instead of just the ids. 3:17 So let's change id to item in this foreach loop. 3:22 We've already changed or get_item_html to use the media id instead of the array key, 3:28 so let's modify this function to just pass the single variable Item. 3:33 We also need to update our function so it only receives one argument. 3:41 Go back to functions, and will scroll down to our get item HTML. 3:46 We'll remove the ID and just keep the item. 3:53 Let's take a quick peek in the browser to make sure our home page is still working. 3:58 We refresh and the four random items change, but if we try to go to the catalog 4:03 page, we see that our changes to the get HTML function broke our catalog pages. 4:08 So let's fix those. 4:14 Open catalog.php. 4:15 Scroll down. 4:19 And where we're using the get_item_html, 4:21 we're going to pass just the item and not the ID. 4:25 Now we go back to our browser and refresh our page. 4:29 Great, our catalog pages are working again. 4:34