How to Correctly Use PDO Prepared Statements with MySQL LIKE Queries in PHP?

PHP PDO Prepared Statement -- MySQL LIKE Query

When working with PDO prepared statements in PHP, implementing a LIKE query using the MySQL driver can be challenging. This article will explore the required syntax and provide a solution to assist developers in resolving any issues they may encounter.

Problem Formulation

A developer is attempting to execute a search query using PDO's MySQL driver. The query works flawlessly when executed directly through the MySQL client, but it fails to return any results when executed using PHP.

PDO Prepared Statement Syntax for MySQL LIKE Query

Unlike string-replacement, prepared statements in PHP PDO transport data separately from the query. Therefore, you should avoid using double quotes or single quotes when binding values to a LIKE statement. The correct syntax for binding a value to a LIKE statement in PHP PDO is as follows:

$searchTerm = '%value%';
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => $searchTerm));

Incorrect Attempts

The following syntaxes attempted by the developer are incorrect and will not produce the desired results:

$searchTerm = '%value%';
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => '"%' . $searchTerm . '%"', '1');

$searchTerm = '%value%';
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => $searchTerm));

Solution

To resolve this issue, use the following code:

$searchTerm = '%value%';
$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => $searchTerm));

Explanation

Prepared statements in PDO do not simply perform string-replacements. Instead, they handle data separately from the query. Quotes are necessary only when embedding values into the query itself. By omitting the quotes in the prepared statement binding, PDO is able to correctly transport the '%value%' string to the database for use in the LIKE comparison.

The above is the detailed content of How to Correctly Use PDO Prepared Statements with MySQL LIKE Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!