How to Correctly Use the LIKE Operator with PDO in PHP?

Implementing LIKE Queries with PDO

When using the LIKE operator in Structured Query Language (SQL), the percent sign (%) is employed as a wildcard character to match zero or more occurrences of a substring. In PHP with PDO (PHP Data Objects), the LIKE operator can be utilized to perform pattern matching against a database column.

Problem Formulation

A user encounters an issue while attempting to utilize the LIKE operator in PDO. Despite ensuring that the variable values contain the desired search terms and verifying the functionality of other PDO queries, the LIKE query returns no results.

Correct Query Syntax

To resolve the issue, it's important to enclose the variable values being matched with the percent (%) wildcard within the $params array rather than embedding them in the query string itself. Here's the correct syntax:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

Explanation

In the original query, the % wildcards were embedded in the query string. However, PDO prepares the query by quoting the values, resulting in a query resembling:

SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%'

This incorrect syntax prevents the % wildcards from functioning correctly as wildcards, leading to no matches being found. By including the wildcards in the $params array instead, the percent signs are not quoted, allowing them to perform their intended wildcard matching functionality.

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