Using Wildcards With PHP’s PDO Object & Prepared Statements.

This is a short PHP tutorial on how to use the wildcard character when using prepared statements with the PDO object. In this guide, I will show you how to bind the wildcard and the string you are attempting to match to a given parameter.

The LIKE comparison operator.

As you probably already know, the LIKE comparison operator is often used for simple pattern matching. When using the LIKE comparison operator, developers will often use the wildcard character % like so:

1	SELECT name FROM users WHERE name LIKE '%ay%';

In the example above, I am selecting all users that have the characters “ay” somewhere in their name. By placing a wildcard character at both ends of my string, I am instructing MySQL to return anything with the string “ay” in it, regardless of what characters come before or after it. If I ran the query in question, I would probably receive results such as Blayne, Kayla and Ray.

PDO & Prepared Statements.

If you’re familiar with PHP’s outdated mysql functions, then it is fair to say that you probably used wildcards like so:

1 2 3 4 5 6 7 8

//The name we are searching for. $name = 'John';   //Our SQL query. $sql = "SELECT name FROM users WHERE name LIKE '%$name%'";   //Query MySQL. $result = mysql_query($sql);

However, what if you want to use the PDO object and prepared statements?

Well, it’s actually pretty simple:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

//The name that we will be searching for. $name = 'John';   //Our statement, which contains the LIKE comparison operator. $sql = "SELECT name FROM users WHERE name LIKE :name"; //Here is where we add our wildcard character(s)! $name = "%$name%"; //We prepare our SELECT statement. $statement = $pdo->prepare($sql); //We bind our $name variable to the :name parameter. $statement->bindValue(':name', $name); //Execute statement. $statement->execute(); //Fetch the result. $results = $statement->fetchAll(PDO::FETCH_ASSOC);

A quick overview of the code above:

1. In this example we are searching for the string “John”.
2. We created our SELECT statement. As you can see, it contains a simple LIKE comparison operator.
3. We added the wildcard character to both sides of our $name variable. This means that our $name variable is now “%John%”.
4. We prepared our statement using the PDO:prepare method.
5. We binded the value of our $name variable to the name parameter in our SELECT statement.
6. We executed the statement.
7. We retrieved our results from the statement that we executed.