php - 使用 pdo fetch_group 将 sql 计数保存为单独的变量

Question

我正在尝试使用 php PDO::FETCH_GROUP 执行 sql COUNT 以避免使用三个单独的 sql 查询

$genders = $db->query('SELECT COUNT(memberID), gender FROM members WHERE gender IS NOT NULL GROUP BY gender')->fetchAll(PDO::FETCH_GROUP);

这个数组看起来像:

Array ( [3] => Array ( [0] => Array ( [gender] => Female [0] => Female ) [1] => Array ( [gender] => Male [0] => Male ) ) [2] => Array ( [0] => Array ( [gender] => Unknown [0] => Unknown ) ) )

虽然我可以在另一个 foreach 循环中使用 foreach 来打印它，但这并不是我真正想要的。

我也尝试过将 bindColumn 与 while 循环一起使用:

$stmt = $db->prepare('SELECT m.gender, COUNT(*) as count FROM members m WHERE m.gender IS NOT NULL GROUP BY m.gender');
$stmt->execute();
$stmt->setFetchMode(PDO::FETCH_BOUND);
$stmt->bindColumn('count', $count);
$stmt->bindColumn('gender', $gender);
while ($row = $stmt->fetch())
    {
        echo $gender.' - '.$count.' ';
    }

但是尽管它们都有效，但我想知道是否可以将每个计数保存在相关变量中？

例如$femaleCount、$maleCount、$unknown 等

Answer 1

您需要 PDO::FETCH_KEY_PAIR 而不是 FETCH_GROUP .该链接指向我的文章，其中列出了所有获取模式，解释了它们的用途和可能的用途。

$sql = 'SELECT gender, COUNT(*) FROM members WHERE gender IS NOT NULL GROUP BY gender';
$data = $db->query($sql)->fetchAll(PDO::FETCH_KEY_PAIR);
foreach ($data as $gender => $count)
{
    echo "$gender - $count\n";
}