1 00:00:00,580 --> 00:00:05,280 In the last video we refactored our index page to pull only the four random 2 00:00:05,280 --> 00:00:08,980 items from our database, instead of pulling all of the items and 3 00:00:08,980 --> 00:00:12,000 allowing PHP to narrow down the list. 4 00:00:12,000 --> 00:00:16,240 This can really speed up our response time as our database grows. 5 00:00:16,240 --> 00:00:18,900 We never want to pull more information than you need. 6 00:00:20,440 --> 00:00:24,080 Let's apply this same principle to the category pages. 7 00:00:24,080 --> 00:00:27,040 Instead of pulling all the items from the database, 8 00:00:27,040 --> 00:00:29,820 let's pull only the items in a specific category. 9 00:00:30,950 --> 00:00:34,157 Back in workspaces, we'll be working in the functions.php file. 10 00:00:35,345 --> 00:00:37,200 Let's duplicate our full catalog array again. 11 00:00:42,770 --> 00:00:45,960 This time we'll name it category_catalog_array. 12 00:00:51,030 --> 00:00:54,471 The first thing we need to do is filter this by category. 13 00:00:54,471 --> 00:00:57,158 We're going to be passing the category variable, so 14 00:00:57,158 --> 00:00:58,780 let's add that as a parameter. 15 00:01:02,241 --> 00:01:05,494 Next, we need to change this to a prepare method. 16 00:01:09,430 --> 00:01:13,298 This way we can be sure to filter the category data we are accepting as our 17 00:01:13,298 --> 00:01:14,580 variable. 18 00:01:14,580 --> 00:01:16,405 We'll space out these SQL commands so 19 00:01:16,405 --> 00:01:19,266 that they're easier to read when we add our new statement. 20 00:01:22,519 --> 00:01:23,580 And we'll add our where. 21 00:01:25,210 --> 00:01:28,480 Where category equals our placeholder. 22 00:01:29,560 --> 00:01:31,439 Next, we'll use our bindParam again. 23 00:01:36,770 --> 00:01:40,960 We use 1 as the first argument to specify the placeholder. 24 00:01:40,960 --> 00:01:43,810 And for the second argument we use the category variable. 25 00:01:46,040 --> 00:01:49,240 The category in the database is stored capitalized. 26 00:01:49,240 --> 00:01:52,320 And the category we are passing is lowercase. 27 00:01:52,320 --> 00:01:57,280 We don't want to check the case at all, so we'll add two functions just to be safe. 28 00:01:57,280 --> 00:01:59,690 One in SQL and one in PHP. 29 00:01:59,690 --> 00:02:01,560 The one in PHP we've seen before. 30 00:02:02,700 --> 00:02:03,840 String to lower. 31 00:02:03,840 --> 00:02:08,221 We'll add this to the top of our function to make sure that our category that we're 32 00:02:08,221 --> 00:02:09,631 passing in is lowercase. 33 00:02:15,432 --> 00:02:18,281 The SQL version of this function is just lower. 34 00:02:18,281 --> 00:02:20,083 We'll place that around our category. 35 00:02:24,690 --> 00:02:28,434 Finally, let's specify our data type. 36 00:02:28,434 --> 00:02:35,046 We'll use PDO::PARAM_STR, for string. 37 00:02:35,046 --> 00:02:36,340 And now we're ready. 38 00:02:42,189 --> 00:02:45,430 Let's call this new function from our catalog page, and test things out. 39 00:02:46,730 --> 00:02:50,727 We're still going to use the call to the full catalog array if no category is 40 00:02:50,727 --> 00:02:55,052 specified, so let's move that catalog line down below the first conditional. 41 00:03:00,238 --> 00:03:03,302 Now we'll add a new conditional, to check our section. 42 00:03:03,302 --> 00:03:12,600 If (empty($section)), then we want to use our full catalog array. 43 00:03:14,670 --> 00:03:16,881 Else, if our section variable is set, 44 00:03:16,881 --> 00:03:19,902 we're going to use our category_catalog_array. 45 00:03:28,001 --> 00:03:29,506 And we'll pass it the section. 46 00:03:33,159 --> 00:03:36,160 Now we need to adjust our foreach statement, and simplify it. 47 00:03:38,000 --> 00:03:41,220 We already have the items we want in our catalog array, so 48 00:03:41,220 --> 00:03:44,050 we can remove this call to our array_category function. 49 00:03:45,080 --> 00:03:49,402 Then we'll change our foreach loop, to loop through our catalog. 50 00:03:49,402 --> 00:03:56,325 We'll name each item, item, and then we'll pass that item to our get_item_html. 51 00:03:58,388 --> 00:04:01,278 Great, now let's go back to our browser and check this out. 52 00:04:03,667 --> 00:04:06,150 On the Books page, we see just the books. 53 00:04:07,670 --> 00:04:09,750 On the Movies page, just the movies. 54 00:04:11,410 --> 00:04:13,330 And on the Music, just the music. 55 00:04:14,920 --> 00:04:19,260 The only problem is that our items are no longer sorted alphabetically. 56 00:04:19,260 --> 00:04:22,120 Our array category function was doing that. 57 00:04:22,120 --> 00:04:26,315 So let's add sort in the form of a SQL order by statement. 58 00:04:26,315 --> 00:04:34,509 Back in our functions.php, we're going to add a new SQL command, ORDER BY. 59 00:04:37,012 --> 00:04:39,280 We want to order by title. 60 00:04:39,280 --> 00:04:43,390 We were also removing the prepositions a, an, and the. 61 00:04:43,390 --> 00:04:45,680 So, let's do that with SQL as well. 62 00:04:45,680 --> 00:04:47,800 For our purpose, a simple REPLACE should work. 63 00:04:49,190 --> 00:04:51,030 REPLACE is case sensitive. 64 00:04:52,030 --> 00:04:59,840 So, we'll replace any occurrence of the capitalized 'The ', with an empty string. 65 00:04:59,840 --> 00:05:05,150 We can then create a nested replace for a, and an, as well. 66 00:05:05,150 --> 00:05:08,930 We use the previous replace as the item upon which to perform 67 00:05:08,930 --> 00:05:11,170 the next replace operation. 68 00:05:11,170 --> 00:05:13,305 Nesting can get pretty complicated, so 69 00:05:13,305 --> 00:05:15,637 make sure you use spaces to your advantage. 70 00:05:32,728 --> 00:05:34,780 Let's check this out once more in the browser. 71 00:05:36,870 --> 00:05:40,680 Perfect, our categories are all filtered and sorted alphabetically. 72 00:05:42,640 --> 00:05:45,940 Since we're no longer using the function that sorted our catalog, 73 00:05:45,940 --> 00:05:48,940 let's go add this ORDER BY to our full catalog array. 74 00:05:50,790 --> 00:05:53,850 We'll copy this entire ORDER BY with all three REPLACEs. 75 00:06:16,357 --> 00:06:18,580 Now let's go back and check out our full catalog. 76 00:06:21,790 --> 00:06:24,310 And this too is sorted alphabetically. 77 00:06:24,310 --> 00:06:27,880 Great, now we're ready to add our first feature, pagination.