Why does my PHP PDO prepared statement with MySQL LIKE query return no results?

PHP PDO Prepared Statement - MySQL LIKE Query

Issue:
When executing a MySQL LIKE query using PHP's PDO, the search returns no results.

Query:

<code class="sql">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;</code>

PHP Code:

<code class="php">$sql = 
'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';

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

Solution:
The mistake lies in the value assigned to the :searchTerm parameter in the execute() method. Double quotes are not necessary when using prepared statements.

Corrected PHP Code:

<code class="php">$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));</code>

Explanation:
Prepared statements handle data separately from the query. They do not perform string replacements. Quotes are only added when embedding values directly into the query.

The above is the detailed content of Why does my PHP PDO prepared statement with MySQL LIKE query return no results?. For more information, please follow other related articles on the PHP Chinese website!