MySQL WHERE IN () + AND , PDO returns only one row

Question

following query returns all wanted results if entered in phpmyadmin:

SELECT     postid, voting 
FROM       postvotes 
WHERE      userid = 1 
AND        postid IN 
                   (1007,1011,1012,1013,1014,
                   1015,1016,1017,1018,1019,1020,1021,1023,1025,1026,
                   1027,1028,1029,1030,1031)

But PDO fails to fetchAll(). It just returns the first match like fetch().

What's wrong?

PHP Code:

private function userPostVotings( $postIDs ) {

// $postIDs contains a string like 1,2,3,4,5,6,7...
// generated through implode(',', idArray)

  try {

    $userPostVote = $this->_db->prepare('SELECT postid, voting 
    FROM postvotes 
    WHERE userid = ? 
    AND postid IN ( ? )');

    $userPostVote->setFetchMode(\PDO::FETCH_ASSOC);
    $userPostVote->execute( array( $this->_requester['id'], $postIDs ) );

    while ( $res = $userPostVote->fetch() ) { 

        var_dump( $res ); 

    }

  } catch (\PDOException $p) {}

}

If I echo out the query used in this method and fire it through phpmyadmin I get the correct number of results. However PDO gives just the first. No matter if a loop with fetch() or fetchAll().

if the query ran fine, then is php error, post you php code, I guess you are talking about php pdo extension — Emilio Gort, Aug 09 '13 at 03:33
possible duplicate of [PHP PDO: Can I bind an array to an IN() condition?](http://stackoverflow.com/questions/920353/php-pdo-can-i-bind-an-array-to-an-in-condition) — hjpotter92, Aug 09 '13 at 03:47

score 3 · Answer 1 · edited May 23 '17 at 11:58

3

You cannot bind array in prepared statements in PDO.

Reference: Can I bind an array to an IN() condition?

edited May 23 '17 at 11:58

Community

1
1

answered Aug 09 '13 at 03:46

Shivam

2,134
1
18
28

There is no array. Implode returns a string of numbers separated by a comma. This string is passed to the method. – user2429266 Aug 09 '13 at 03:50

score 2 · Accepted Answer · answered Aug 09 '13 at 03:46

it is not PDO's fetchAll() of course, but your query.

Which is not

IN (1007,1011,1012,1013,1014)

but

IN ('1007,1011,1012,1013,1014')

and of course it will find only first value as this string will be cast to the first number

One have to create a query with placeholders representing every array member, and then bind this array values for execution:

$ids = array(1,2,3);
$stm = $pdo->prepare("SELECT * FROM t WHERE id IN (?,?,?)");
$stm->execute($ids);

To make this query more flexible, it's better to create a string with ?s dynamically:

$ids = array(1,2,3);
$in  = str_repeat('?,', count($arr) - 1) . '?';
$sql = "SELECT * FROM table WHERE column IN ($in)";
$stm = $db->prepare($sql);
$stm->execute($ids);
$data = $stm->fetchAll();

MySQL WHERE IN () + AND , PDO returns only one row

2 Answers2

Linked