我试图创建一个简单的谷歌条形图使用数据从MySQL数据库。谷歌提供了一个例子，我正试图以此为基础构建我的博客。参考"一个简单的例子"
我希望能够显示每个学校的男生和女生的总数。
这是我的PHP代码检索数据从MySQL:
<?php
$pdo=new PDO("mysql:dbname=trek;host=localhost","USERNAME","PASSWORD");
$stmt=$pdo->prepare("SELECT school, gender, count(*) as count from participant group by school, gender;");
$stmt->execute();
$results=$stmt->fetchALL(PDO::FETCH_ASSOC);
echo json_encode($results);
?>
这回报:
[
{
school: "School A",
gender: "F",
count: "1"
},
{
school: "School A",
gender: "M",
count: "2"
},
{
school: "School B",
gender: "M",
count: "2"
},
{
school: "School B",
gender: "F",
count: "10"
}
]
但是我需要以以下格式出现:
[
{
school: "School A",
m: "2",
f: "1"
},
{
school: "School B",
m: "2",
f: "2"
}
]
我对使用谷歌图表有点陌生，我愿意听取任何建议。
提前感谢!
试试这个:
SELECT
school,
sum(if(gender='M',1,0)) as m,
sum(if(gender='F',1,0)) as f
FROM
participant
GROUP BY
school
我明白了。问题是在我的SQL语句
SELECT school,
sum(case when gender = 'm' then 1 else 0 end) male,
sum(case when gender = 'f' then 1 else 0 end) female
FROM participant
GROUP BY school;