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:
The LIKE comparison operator.
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:
//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:
//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:
- In this example we are searching for the string “John”.
- As you can see, it contains a simple LIKE comparison operator.
- We added the wildcard character to both sides of our $name variable. This means that our $name variable is now “%John%”.
- We prepared our statement using the PDO:prepare method.
- We binded the value of our $name variable to the name parameter in our SELECT statement.
- We executed the statement.
- We retrieved our results from the statement that we executed.
As you can see, there is not much of a difference! Hopefully, this helped to clear a few things up!