Why Do My PDO LIKE Queries Fail, and How Can I Fix Them?

Implementing LIKE Queries in PDO

Implementing LIKE queries in PDO may pose challenges. Consider the example query:

$query = "SELECT * FROM tbl WHERE address LIKE '%?%' OR address LIKE '%?%'";
$params = array($var1, $var2);
$stmt = $handle->prepare($query);
$stmt->execute($params);

Despite the validity of the PDO connection and functioning of other queries, LIKE queries may fail to return results.

Fixing the Syntax

To correct the query, ensure that the % signs are included in the $params array, rather than the query:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

Understanding the Issue

In the original query, the % signs are placed within the query itself. However, when the query is prepared, the values from $params are quoted inside the already-quoted string. This results in a query like:

SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%'

This query will not return any results because the values are not properly escaped. By placing the % signs in the $params array, they become part of the values being escaped, and the query will execute correctly.

The above is the detailed content of Why Do My PDO LIKE Queries Fail, and How Can I Fix Them?. For more information, please follow other related articles on the PHP Chinese website!