mysql from in pdo,MySQL WHERE IN（）+ AND，PDO只返回一行

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().

解决方案

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();