How to Properly Structure LIKE Queries with Parameters in PDO?

Implementing the LIKE Query in PDO

When using PDO to execute queries involving the LIKE operator, it's essential to understand how to properly structure the query and the parameters. Consider the following query:

"$query = "SELECT * FROM tbl WHERE address LIKE '%?%' OR address LIKE '%?%'";
$params = array($var1, $var2);"

Error:
The query results in no records being returned because the percentage signs (%) are included within the query itself, rather than being specified as parameters.

Solution:

To rectify this issue:

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

Explanation:

By including the percentage signs within the parameters, we ensure that they're properly treated as placeholders when the query is executed. This instructs PDO to substitute the corresponding values with the actual search terms, allowing the LIKE operation to be performed correctly.

When examining the generated query from your original code, you would notice something along the lines of "SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%'," where the prepared statement is mistakenly quoting the values within an already quoted string. By following the correct approach outlined above, you can resolve this issue and successfully execute LIKE queries in PDO.

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