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

PHP PDO Prepared Statement - MySQL LIKE Query

In PHP, using prepared statements with PDO for MySQL LIKE queries can be challenging. This article addresses a common issue faced when trying to execute a LIKE query using prepared statements.

Consider the following query:

SELECT    hs.hs_pk, 
          hs.hs_text, 
          hs.hs_did, 
          hd.hd_did, 
          hd.hd_text, 
          hv.hv_text, 
          hc.hc_text 
FROM      hs 
LEFT JOIN hd 
 ON       hs.hs_did = hd.hd_did 
LEFT JOIN hd 
 ON       hd.hd_vid = hv.hv_id 
LEFT JOIN hc 
 ON       hd.hd_pclass = hc.hc_id
WHERE     hs.hs_text LIKE "%searchTerm%"
LIMIT 25;

This query successfully searches for strings containing the search term when executed directly in the MySQL client. However, when translating this query to PDO with prepared statements, results are not returned.

The provided PHP code illustrates failed attempts to execute the LIKE query using various syntaxes. The issue lies in the syntax used for specifying the search term in the prepared statement.

Incorrect attempts include:

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

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

The correct approach is to use the following syntax:

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

Explanation:

Prepared statements do not perform simple string replacements. Data is transferred separately from the query, making quotes unnecessary when embedding values. Quotes are only required when the actual quoted value needs to be inserted into the query.

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