1

When I AND together two LIKEs in a SQL query, it works fine if I execute it straight on the server. Things get strange when I use PDO to bind variables.

On the server the following works as expected

SELECT * FROM CarParkMain WHERE Make LIKE '%b%' AND Model LIKE '%02 (e10)%'

The following statements bring expected results in my PHP

SELECT * FROM CarParkMain WHERE Model LIKE :b
SELECT * FROM CarParkMain WHERE Make LIKE :a
SELECT * FROM CarParkMain WHERE Make LIKE '%b%' AND Model LIKE '%02 (e10)%'

The following statement does not work (zero results)

SELECT * FROM CarParkMain WHERE Make LIKE :a AND Model LIKE :b

This statement returns the results of the Make LIKE only, suggesting the Model LIKE returns nothing (contradicting with statement above where the Model LIKE is alone, that brings results)

SELECT * FROM CarParkMain WHERE Make LIKE :a OR Model LIKE :b

I can create the issue with the code below. Any ideas? Am I missing something stupid?

$prepared = $pdo->prepare("SELECT * FROM CarParkMain WHERE Make LIKE :a AND Model LIKE :b");

$filter = "%b%";
$prepared->bindParam(":a", $filter);

$filter = "%02 (e10)%";
$prepared->bindParam(":b", $filter);

$prepared->execute();

$rows = $prepared->fetchAll();
Nathan Finch
  • 11
  • 2
  • What is your question exactly? – u_mulder Nov 13 '15 at 19:45
  • 2
    It looks like you're running afoul of a variable reference `$filter`. Use a different variable name than `$filter` for `:b` or use `bindValue()` instead because the reference's value has changed by the time the statement executes. http://php.net/manual/en/pdostatement.bindparam.php – Michael Berkowski Nov 13 '15 at 19:46
  • Damn that was fast. And it's fixed. Thanks a tonne. Been hitting my head on the desk for hours. If you can find a full explanation that would be great! – Nathan Finch Nov 13 '15 at 19:49
  • Glad to help. It's linked up at the top now - an answer with examples similar to your situation. – Michael Berkowski Nov 13 '15 at 19:50
  • bind_param is just establishing a reference to the bound variable. it doesn't "copy" the value in the variable at the time of binding. that value is looked up and copied when you EXECUTE the query, which means you're running the query using the final value of `$filter` for both params, so you're effectively doing `like %b% and like %02 (e10)%` – Marc B Nov 13 '15 at 19:53
  • bindParam bind like it said... the param, so the variable. if you change the content of this variable before executing it, then you'll execute with the last value – Blag Nov 13 '15 at 19:53

0 Answers0