Search code examples
phpmysqlpdobindparam

How to bind LIKE values using the PDO extension?

In this query 

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

I'm trying to bind the variable '$partial%' with PDO. Not sure how this works with the % at the end.

Would it be 

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

where :partial is bound to $partial="somet"

or would it be 

select wrd from tablename WHERE wrd LIKE ':partial'

where :partial is bound to $partial="somet%"

or would it be something entirely different?

Solution

  • You could also say:

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

    to do the string joining at the MySQL end, not that there's any particular reason to in this case.

    Things get a bit more tricky if the partial wrd you are looking for can itself contain a percent or underscore character (since those have special meaning for the LIKE operator) or a backslash (which MySQL uses as another layer of escaping in the LIKE operator — incorrectly, according to the ANSI SQL standard).

    Hopefully that doesn't affect you, but if you do need to get that case right, here's the messy solution:

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