MySQL WHERE IN()+ AND，PDO只返回一行 [英] MySQL WHERE IN () + AND , PDO returns only one row

问题描述

如果在phpmyadmin中输入以下查询，则会返回所有所需结果:

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)

但是PDO无法获取fetchAll().它只返回第一个匹配项，例如fetch().

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

怎么了?

PHP代码:

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) {}

}

如果我回显了此方法中使用的查询，并通过phpmyadmin触发了该查询，则会得到正确数量的结果.但是，PDO仅给出第一个.不管是使用fetch()还是fetchAll()循环.

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

推荐答案

它当然不是PDO的fetchAll()，而是您的查询.

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

不是

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

但是

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

要使此查询更灵活，最好动态地使用?s创建一个字符串:

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只返回一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！