PHP - 将 SQL GROUP BY 作为数组返回？

Question

我正在尝试使用 GROUP BY 作为数组返回 MySQL 查询。

我的表格如下所示:

user_id | type
--------------------
1       | test
1       | test
2       | test
1       | hello
1       | helloworld

以及我的代码和查询:

$number_of_workouts = array();
$query = mysqli_query(connect(),"SELECT type, COUNT(*) FROM `log` WHERE `user_id` = $user_id GROUP BY type");
$number_of_workouts = mysqli_fetch_assoc($query);

return $number_of_workouts;

上面的代码不会返回列出所有类型的数组，它只会返回一种类型的编号(假设 $user_id = 1。

如何以数组形式返回查询结果？

Answer 1

mysqli_fetch_assoc($query);

从结果集中仅获取 1 行

如果您想要结果集中的所有行，则必须循环每一行并将其添加到堆栈中，如下所示:

$number_of_workouts = array();
$query = mysqli_query(connect(),
    "SELECT type, COUNT(*) AS count
     FROM `log`
     WHERE `user_id` = $user_id
     GROUP BY type"
);

$array = array();

while ($number_of_workouts = mysqli_fetch_assoc($query)) {
    $array[$number_of_workouts['type']] = $number_of_workouts['count'];
}

// Now you have all results like you want in the variable array:
print_r($array);

// print count of type test:
echo $array['test'];

或者您尝试mysqli_fetch_all() ( http://www.php.net/manual/en/mysqli-result.fetch-all.php )

(抱歉有很多更新)