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

DDD
Release: 2024-10-31 10:41:02
Original
1019 people have browsed it

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>
Copy after login

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>
Copy after login

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template