首先,感谢您的观看,我有一个mysql问题,我真的不知道如何解决。如果有人能帮我,我会非常感激的。
我的mysql数据库中有以下两个表(见下面的链接)
http://www.rodneywormsbecher.com/sql-tables.jpg
我希望实现的输出如下:
array ( [0] => category_name [total_userhours] => count(id) userhours [1] => category_name [total_userhours] => count(id) userhours [2] => category_name [total_userhours] => count(id) userhours )
So the first result in the array has the category name and under it, it makes a variable that counts all userhours that have the userhour_category_id.
Thanks in advance,Rodney
/ edit I got 2 working solutions from the replies:
$data = array();
foreach ($Database->customQuery("SELECT * FROM userhour") as $r) {
$cat_id = $r['userhour_category_id'];
$cat_result_set = $Database->fetchSingleArray($Database->customQuery("SELECT * FROM userhour_category WHERE id={$cat_id}"));
$cat_name = $cat_result_set['category_name'];
$count = $Database->fetchSingleArray($Database->customQuery("SELECT COUNT(id) AS total FROM userhour WHERE userhour_category_id={$cat_id}"));
$data[$cat_name] = $count['total'];
}
print_r($data);
$Sql1 = "SELECT c.category_name, count(*) AS total_userhours
FROM `userhour` u
JOIN `userhour_category` c
ON (u.userhour_category_id = c.id)
GROUP BY u.userhour_category_id";
$test = $Database->fetchAllArray($Database->customQuery($Sql1));
print_r($test);
两者都很有魅力,但我更喜欢用sql的方式。
输出:
阵列
(
[平面设计]=>15
[网页设计]=>9
[新闻稿]=>1
)
阵列
(
[0]=>数组
(
[类别名称]=>网页设计
[总用户小时数]=>9
)
[1]=>数组
(
[类别名称]=>平面设计
[总用户小时数]=>15
)
[2]=>数组
(
[分类名称]=>新闻稿
[总用户小时数]=>1
)
)
谢谢大家。
最佳答案
您可以按类别对userhour
进行分组,然后对每个组进行计数:
SELECT c.category_name, count(*) AS total_userhours
FROM `userhour` u
JOIN `userhour_category` c
ON (u.userhour_category_id = c.id)
GROUP BY u.userhour_category_id
我没有测试查询,但它应该可以工作。
关于php - MySQL SELECT和顺序作为关联数组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35917314/