Difficulty using an array as part of PDO "IN" query

Question

I'm trying to query a MySQL databse using an array.

$array=array('Group1','Group2','Group3');
$inQuery=implode(",",$array);
//$inQuery='Group1'; //This returns the expected result, but is obviously not an array

$data=array($inQuery);
try {
  $STH = $this->DBH->prepare('SELECT GroupName FROM myTable WHERE GroupName IN(?)');            
  $STH->execute($data);
  /* Output results*/
}
catch(PDOException $e) { /*Panic!*/ }

I am not getting any error messages, just 0 results. Any help would be appreciated!

The SQL `IN` clause doesn't support using a single variable for a list of values -- you'd need `?` for each array value. — OMG Ponies, Jun 27 '11 at 20:28

score 1 · Answer 1 · answered Jun 27 '11 at 20:55

You could try this way - not tested - :

// To fetch your array data
$array=array('Group1','Group2','Group3');
try {
  $STH = $this->DBH->prepare("SELECT GroupName FROM myTable WHERE GroupName IN (?)");            
  $STH->execute($array);
  while($lines=$STH->fetch($this->DBH->FETCH_OBJ))
{
        echo $lines->GroupName.'<br />';
}

}
catch(PDOException $e) { 
/*Panic!*/ 
echo 'ERR: ' .$e->getMessage().'<br/>';
}

score 0 · Accepted Answer · answered Aug 02 '11 at 18:11

Just in case anyone else ever comes across this....

It appeared to be an issue with escaping the imploded array.

$array=array('Group1','Group2','Group3');
$inQuery=implode(",",$array);
$inQuery="'".$inQuery."'"; //Solved the issue.

$data=array();
try {
  $STH = $this->DBH->prepare('SELECT GroupName FROM myTable WHERE GroupName IN($inQuery)');            
  $STH->execute($data);
  /* Output results*/
}
catch(PDOException $e) { /*Panic!*/ }

Difficulty using an array as part of PDO "IN" query

2 Answers2

Linked