Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?

PHP PDO Prepared Statement - MySQL LIKE Query: A Conundrum

When working with PHP's PDO class (MySQL driver), executing a MySQL LIKE query using a prepared statement can be a perplexing task. The seemingly straightforward syntax often fails to yield the desired result.

Consider the following query that seamlessly retrieves data using the MySQL client:

SELECT   ...
FROM      hs
...
WHERE     hs.hs_text LIKE "%searchTerm%"
LIMIT 25;

The Conundrum

However, upon porting this query to PHP, you may encounter the frustration of not being able to return any results, regardless of the syntax you attempt. The below code demonstrates this issue:

$sql = 
    'SELECT   ...
    FROM      hs
    ...
    WHERE     hs.hs_text LIKE :searchTerm
    LIMIT 25';

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

Attempts to provide the search term as CONCAT('%', ?, '%'), "%:searchTerm%", or ":searchTerm", while modifying the execute line accordingly, prove futile.

The Solution

The key to resolving this conundrum lies in understanding how prepared statements handle data. They convey data separately from the query, eliminating the need for quotes when embedding values.

Therefore, the correct solution is:

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

By removing the enclosing double quotes from the search term parameter, you now harness the power of prepared statements, allowing you to successfully execute your LIKE query.

The above is the detailed content of Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?. For more information, please follow other related articles on the PHP Chinese website!