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!