How to Correctly Use PDO Prepared Statements for MySQL LIKE Queries in PHP?

PHP PDO Prepared Statement -- MySQL LIKE Query

PDO prepared statements offer a secure and efficient way to execute SQL queries with dynamic parameters. When working with MySQL LIKE queries, employing proper syntax is crucial.

Question:

A developer is facing challenges in executing a MySQL LIKE query using PDO in PHP, despite the query functioning correctly in the MySQL client. The code is failing to return results, and various syntax variations have been attempted unsuccessfully.

Answer:

The incorrect syntax in the code lies within the prepare and execute statements. The issue is the unnecessary use of double quotes and incorrect parameter binding. Here's the corrected code:

$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.'%'));

Explanation:

Prepared statements work by sending the query to the database without any parameter values embedded in it. The parameters are sent separately to be bound by the database engine. Adding double quotes to the parameter value in the execute statement is incorrect as PDO will automatically enclose the parameter value in quotes if needed.

Additionally, the LIKE operator requires percentages around the search term to allow for partial matches. In the incorrect code, the search term is incorrectly bounded with double quotes instead of the necessary percentage signs.

By correcting these syntax errors, the PDO prepared statement can be used to successfully execute MySQL LIKE queries.

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