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