1

I have tried writing the code below 100 different ways and cannot figure out the proper syntax. It seems no matter how I write this, the $loggeduser is not passed to the WHERE username. What am I doing wrong? What is the proper syntax? 

$query = $db->prepare('SELECT * FROM sites WHERE username = ? AND name LIKE ? OR login LIKE ? AND category="te"');
$query->bindValue(1, $loggeduser);
$query->bindValue(2, '%'.$_POST[search].'%');
$query->bindValue(3, '%'.$_POST[search].'%');
$query->execute();}
Bruce
  • 1,039
  • 1
  • 9
  • 31
  • 2
    try `bindParam` instead of `bindValue` – mister martin Jul 01 '16 at 21:00
  • That returns a "Fatal error: Cannot pass parameter 2 by reference" – Bruce Jul 01 '16 at 21:06
  • The error means that the 2nd argument is expected to be a reference to a variable.`$_POST['search']` is a variable but your code wants PHP to interpret the constant "search". You're missing the quotes around the identifier. – Jay Blanchard Jul 01 '16 at 21:09
  • with or without quotes the error exists. If I switch them to bindValue instead of bindParam they work, but the $loggeduser does not bind. I have tried both bindValue and bindParam on just the $loggeduser and neither have any effect. I have also echo'd the $_POST['search'] (and $_POST[search]) the variable is passed. – Bruce Jul 01 '16 at 21:17
  • I am completely dumbfounded. I have tried bindParam (which I don't believe should work because of a '%' which is not actually a reference to a variable. I have tried bindValue. I have tried just using bindParam on the first bind, No matter how I do this, the second and thrid bind values work perfect but not the first. I have also echo'd all variables to ensure that values are being returned. I am at a loss. – Bruce Jul 01 '16 at 21:42

1 Answers1

0

According to this comment

When binding parameters, apparently you can't use a placeholder twice (e.g. "select * from mails where sender=:me or recipient=:me"), you'll have to give them different names otherwise your query will return empty handed (but not fail, unfortunately).

This may be your issue, as you're attempting to bind the same value to two separate placeholders. Give the following a try:

$query = $db->prepare('SELECT * FROM sites WHERE username = :username AND name LIKE :name OR login LIKE :login AND category="te"');
$query->bindValue(:username, $loggeduser);
$query->bindValue(:name, '%'.$_POST['search'].'%');
$query->bindValue(:login, '%'.$_POST['search'].'%');
$query->execute();
mister martin
  • 6,197
  • 4
  • 30
  • 63
  • Actually the error was in my failing at understanding SQL. My original code wasn't giving errors, because it was running flawlessly - I was just not expecting the results it was giving. Note that I said it seemed like the $loggeduser variable wasn't being passed.... well as it turned out, the variable was being passed, just not after the OR, I didn't realise I have to write WHERE .... AND .... OR .... AND ... to get the $loggeduser check before and after OR. – Bruce Jul 02 '16 at 05:37
  • @Bruce I see... Glad you solved it! That makes sense. – mister martin Jul 02 '16 at 05:46