Matching Search Patterns

Our search form is set up and ready to go. 0:00 Visitors to our site can enter a search term and 0:03 send that information to our server. 0:05 But our site doesn't know what to do with that information yet. 0:07 Let's filter the user input into a variable on our catalog page. 0:11 Then we can use that variable to start searching the database and 0:15 displaying the results. 0:18 Back in workspaces, we'll open the catalog.php file. 0:20 Let's start by adding another search variable to the top of our page 0:24 directly under the section variable. 0:27 We'll assign this a value of null, then we'll go down a little ways and 0:33 duplicate the conditional that sets our current page. 0:37 We'll change the name of our get variable, from pg to s. 0:46 Then we'll change our filter, to be filter_sanitize_string. 0:53 Finally, we'll change the variable where assigning. 0:59 We'll change this to search. 1:04 After our current page, we come down to the total items. 1:07 We want to count our search results as well, so 1:11 let's modify the get_catalog_count function. 1:13 Open functions.php. 1:17 You'll see our get_catalog_count function at the very top of the page. 1:18 The first thing we need to do is add another optional parameter for search. 1:24 Then we'll add another conditional to our conditional. 1:33 This time we wanna check for search. 1:41 Make sure you change the next one to an else if. 1:46 Again, we'll start with a prepare statement. 1:52 We start with our $sql variable, and then we'll add a WHERE. 2:01 This time we want to retrieve only those items 2:07 where the titles match the search term. 2:10 We want to search the item title, which is contained in a column called title. 2:13 Imagine someone entered the search term, ring. 2:18 We could check if the title were equal to the search term like this. 2:21 That would not perform a partial match. 2:27 That would only work if the entire title of the item were ring. 2:30 Instead of an equal sign, we can use a different operator. 2:34 The LIKE operator. 2:40 The LIKE operator checks if the value in the column name 2:43 matches a particular pattern. 2:46 To use LIKE, you also want to use a wildcard character. 2:48 In sql, the wildcard character is a % sign. 2:52 The % sign means any character. 2:56 So we'll put a wildcard at the beginning and at the end. 3:01 So this particular where clause looks for 3:05 all records where the title contains any characters, followed by ring, 3:07 followed by any characters, and those characters are optional. 3:12 So this would also match titles that start with ring or that end with ring. 3:16 We want to replace this whole block,here, with a placeholder. 3:21 We'll next, need to bind that value to the placeholder. 3:27 We've been using bindParam for this so far. 3:30 That works just fine when the value you want to bind is a variable. 3:33 But we need more than just the search term in this placeholder. 3:36 We also need the percent signs. 3:40 In this case, we can't use bindParam. 3:42 We'll need to use bindValue. 3:45 You can pass a variable to bindValue without any problems. 3:48 There is a subtle difference between how bindValue and bindParam work. 3:52 BindParam binds a placeholder to a specific variable, 3:57 even if that variable changes later. 4:01 BindValue on the other hand, 4:03 binds the placeholder to the value in the variable at the moment of the binding. 4:05 In all the examples we've used before this one, either bindParam or 4:10 bindValue would have worked just fine. 4:14 With bindValue though, we can use concatenation. 4:17 We'll close off our prepare and add our bindValue. 4:21 We want to add our wildcards, followed by our search term and another wildcard. 4:29 If a site visitor searches for the word ring, this code will replace the question 4:36 mark in the query with percent sign, ring, percent sign. 4:40 In the bindValue method, we'll surround it in a single quote. 4:43 We can still specify our type with bindValue, so 4:48 we'll use our PDO PARAM_STRING. 4:51 With this value bound, we can now execute the query and continue on like we have, 4:59 returning the column count. 5:03