implement LIKE query in PDO

Question

I am running problems in implementing LIKE in PDO

I have this query:

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

I checked the $var1 and $var2 they contain both the words I want to search, my PDO is working fine since some of my queries SELECT INSERT they work, it's just that I am not familiar in LIKE here in PDO.

The result is none returned. Do my $query is syntactically correct?

Answer 1

You have to include the % signs in the $params, not in the query:

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

If you'd look at the generated query in your previous code, you'd see something like SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%', because the prepared statement is quoting your values inside of an already quoted string.

Answer 2

Simply use the following:

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

$ar_val = array(':var1'=>$var1, ':var2'=>$var2);
if($sqlprep->execute($ar_val)) { ... }

Answer 3

No, you don't need to quote prepare placeholders. Also, include the % marks inside of your variables.

LIKE ?

And in the variable: %string%

Answer 4

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