0

I have the following statement set up. I have replaces a long list of columns with * to make this more readable. FYI - I already know that there are questions similar to this. They use SINGLE select statements with a SINGLE parameter. Somehow this is different.

$sql = <<<EOM 
SELECT *
    FROM table1
    WHERE StreetName like '%:StreetName_Coml%'
    UNION
    
SELECT *
    FROM table2
    WHERE StreetName like '%:StreetName_Coms%'
    UNION
    
SELECT *
    FROM table3
    WHERE StreetName like '%:StreetName_Farm%'
    UNION
    
SELECT *
    FROM table4
    WHERE StreetName like '%:StreetName_Land%';
EOM;

$p = $db->prepare($sql);

$StreetName = 'tree'
    
$p->bindValue(':StreetName_Coml', $StreetName);
$p->bindValue(':StreetName_Coms', $StreetName);
$p->bindValue(':StreetName_Farm', $StreetName);
$p->bindValue(':StreetName_Land', $StreetName);

$p->execute();

$data = $p->fetchAll(PDO::FETCH_ASSOC);

The query runs, with no PHP errors. But I am getting no results back. I should be getting back 100's of rows. When I run the same query in my database browser I get 100's of rows. There is something in how the parameters are being bound that is not working.

I have tried the following:

  • bindParam instead of bindValue
  • moving the '%' from the SQL statement and into $StreetName. So instead of 'tree' it is '%tree%'
  • using a CONCAT statement like "WHERE StreetName like CONCAT('%',:StreetName_Land,'%')

and various mix and matching of the above.

What am I missing?

TylerH
  • 20,799
  • 66
  • 75
  • 101
NealM
  • 58
  • 7
  • 1
    What you have will not work, but either of your second or third bullet point options should have worked properly (as long as your placeholders were not still in quotes for the second option). – Nick Feb 25 '21 at 22:00

1 Answers1

0

Put the percent signs in the bind, so your SQL is like this, unqouted:

WHERE StreetName like :StreetName

And then your binds are like this:

$p->bindValue(':StreetName', '%' . $StreetName . '%');
Alex Howansky
  • 50,515
  • 8
  • 78
  • 98