Using PHP and MySQL, how do you send a variable into a WHERE... IN clause?

Question

The following SELECT statement works just fine:

try {     
  $sql = $db->prepare('SELECT *
                       FROM classes
                       WHERE classes.id IN (65,70,80)');
  $sql->execute();
  $sublist = $sql->fetchAll(PDO::FETCH_ASSOC);
} catch(Exception $e) {
  echo $e->getMessage();
  die();
}

BUT when I try to send a variable into the WHERE clause, I only get one row back.

In this case $ids is a string that looks like '65,70,80'.

try {     
  $sql = $db->prepare('SELECT *
                       FROM classes
                       WHERE classes.id IN (?)');
  $sql->bindParam(1, $ids);
  $sql->execute();
  $sublist = $sql->fetchAll(PDO::FETCH_ASSOC);
} catch(Exception $e) {
  echo $e->getMessage();
  die();
}

Do I need to change the way I'm fetching the data, or is my syntax wrong?

A-HA! Thanks for that link Mihai. Here's the code that works:

First: instead of passing in a string, put ids into an $ids array.

// Figure out how many ? marks have to be in your query as parameter placeholders.
$count = count($ids);
$arrayOfQuestions = array_fill(0, $count, '?');
$queryPlaceholders = implode(',', $arrayOfQuestions);

try {     
  $sql = $db->prepare('SELECT *
                       FROM classes
                       WHERE classes.id IN (' . $queryPlaceholders . ')');
  $sql->execute($ids);
  $sublist = $sql->fetchAll(PDO::FETCH_ASSOC);
} catch(Exception $e) {
  echo $e->getMessage();
  die();
}

You are binding a string so it will look for `'65,70,80'` Look here http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition — Mihai, Apr 14 '16 at 15:34
if `$ids` is an `array` change your code to `$sql->bindParam(1, implode(',', $ids));` — RST, Apr 14 '16 at 15:38
Please don't edit your question to add an answer. If you have an answer to the question you asked, add it as an *answer* to the question. — spencer7593, Apr 15 '16 at 13:31

maxpovver · Answer 1 · 2016-04-14T15:50:51.013

0

Try this:

try {     
  $sql = $db->prepare('SELECT *
                       FROM classes
                       WHERE classes.id IN (?)');
  $sql->bindParam(1, explode(',', $ids));
  $sql->execute();
  $sublist = $sql->fetchAll(PDO::FETCH_ASSOC);
} catch(Exception $e) {
  echo $e->getMessage();
  die();
}

edited Apr 14 '16 at 15:50

answered Apr 14 '16 at 15:39

maxpovver

1,580
14
25

When I test this I get an "Array to String Conversion" error. The question that Mihai refers to above got me on the right track. – B. Krafft Apr 15 '16 at 13:15

Using PHP and MySQL, how do you send a variable into a WHERE... IN clause?

1 Answers1