Home > Database > Mysql Tutorial > Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?

Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?

Patricia Arquette
Release: 2024-11-30 10:41:13
Original
545 people have browsed it

Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?

PHP PDO Prepared Statement - MySQL LIKE Query: A Conundrum

When working with PHP's PDO class (MySQL driver), executing a MySQL LIKE query using a prepared statement can be a perplexing task. The seemingly straightforward syntax often fails to yield the desired result.

Consider the following query that seamlessly retrieves data using the MySQL client:

SELECT   ...
FROM      hs
...
WHERE     hs.hs_text LIKE "%searchTerm%"
LIMIT 25;
Copy after login

The Conundrum

However, upon porting this query to PHP, you may encounter the frustration of not being able to return any results, regardless of the syntax you attempt. The below code demonstrates this issue:

$sql = 
    'SELECT   ...
    FROM      hs
    ...
    WHERE     hs.hs_text LIKE :searchTerm
    LIMIT 25';

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

Attempts to provide the search term as CONCAT('%', ?, '%'), "%:searchTerm%", or ":searchTerm", while modifying the execute line accordingly, prove futile.

The Solution

The key to resolving this conundrum lies in understanding how prepared statements handle data. They convey data separately from the query, eliminating the need for quotes when embedding values.

Therefore, the correct solution is:

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

By removing the enclosing double quotes from the search term parameter, you now harness the power of prepared statements, allowing you to successfully execute your LIKE query.

The above is the detailed content of Why Doesn't My PHP PDO Prepared Statement Work with MySQL LIKE Queries?. For more information, please follow other related articles on the PHP Chinese website!

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
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template