This course will be retired on June 1, 2025.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses 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);
}
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up