0

Here is my code:

$arr = [val1, val2, val3];
$str = join("','",$arr); //=> val1','val2','val3
$stm = $dbh->prepare("SELECT * FROM mytable WHERE mycol NOT IN ('$str')");
$stm->execute();

Code above works as well. Now I want to pass that variable to the query instead of using it into the query directly. Something like this:

$arr = [val1, val2, val3];
$str = join("','",$arr); //=> val1','val2','val3
$stm = $dbh->prepare("SELECT * FROM mytable WHERE mycol NOT IN (':str')");
$stm->execute(array('str' => $str));

But it doesn't work. There is syntax error (I'm pretty sure the problem is about quotes) ..!

Note: Sometimes $str is empty.

Anyway, how can I handle quotes?

stack
  • 10,280
  • 19
  • 65
  • 117
  • @chris85 Honestly I didn't get your point. But the value of `$str` will be created by `join("','",$vals)` – stack Dec 22 '16 at 22:30
  • Not `NOT IN (':str')` it has to be `NOT IN (:str, :str1, :str2)` then have `$str` be an array and you can just bind it... anonymous placeholders would be easier, `NOT IN (?, ?, ?)` then `$stm->execute($str)` if you can make that an array. – chris85 Dec 22 '16 at 22:30
  • @chris85 Implementing what you are saying isn't easy .. it needs lots of code. – stack Dec 22 '16 at 22:32
  • If you have it as an array why do you need to use the `join`? Just keep it like that.. – chris85 Dec 22 '16 at 22:36
  • @chris85 I need to make a string of that array to use it into `IN()` clause. – stack Dec 22 '16 at 22:38
  • No, no, don't do that. Use a placeholder for each value in the array and then bind the whole array... and yes you are right `I'm pretty sure the problem is about quotes` when you quote a placeholder it isn't a placeholder, it is the literal value. – chris85 Dec 22 '16 at 22:38
  • @chris85 please take a look at this http://stackoverflow.com/questions/907806/php-mysql-using-an-array-in-where-clause#907821 – stack Dec 22 '16 at 22:39
  • You read the note `Do NOT use the code samples presented here.`... – chris85 Dec 22 '16 at 22:40
  • @chris85 That note tries to tell us **don't use variable directly into the query** ..! that's why I'm trying to use placeholder ... – stack Dec 22 '16 at 22:41
  • Yes, you need a placeholder for each value, not one placeholder for all values. The driver escapes all quotes. So if you were to get that implementation to work you'd query against one record of `val1','val2','val3` which I don't think you'd have. – chris85 Dec 22 '16 at 22:42

0 Answers0