How to Bind LIKE Values with the PDO Extension?

Binding LIKE Values with the PDO Extension

When working with LIKE queries, it's important to consider how to bind variable values using the PDO extension. The '%' wildcard character, when placed at the end of the search string, can present some challenges.

In the query provided:

select wrd from tablename WHERE wrd LIKE '$partial%'

The question arises regarding how to bind the ${partial} variable with PDO. It's worth considering three options:

Option 1: Bind with '%' at the end

select wrd from tablename WHERE wrd LIKE ':partial%'

In this option, the parameter :partial is bound to the value $partial="somet", retaining the '%' at the end.

Option 2: Bind without '%' at the end

select wrd from tablename WHERE wrd LIKE ':partial'

In this option, :partial is bound to $partial="somet%", effectively having a fixed '%' at the end of the search string.

Option 3: Use MySQL's CONCAT function

SELECT wrd FROM tablename WHERE wrd LIKE CONCAT(:partial, '%')

This option allows for the concatenation of the search term and '%' to be performed within MySQL itself.

In addition, if the search term you're looking for might contain characters with special meanings in LIKE operators, such as '%', '_', or '', a more complex approach using string escaping is required:

$stmt= $db->prepare("SELECT wrd FROM tablename WHERE wrd LIKE :term ESCAPE '+'");
$escaped= str_replace(array('+', '%', '_'), array('++', '+%', '+_'), $var);
$stmt->bindParam(':term', $escaped);

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