CODEWITHSUNDEEP
phpmysqlgoogle-visualization
R_Marlatt
R_Marlatt

Reputation: 119

Google Bar Chart from MySQL and PHP

I am trying to create a simple Google Bar Chart using data pulled from a MySQL Database. Google provides an example which I am trying to structure mine off of. See "A Simple Example".

I would like to be able to show the total number of Males and Females in each school.

Here is my PHP code for retrieving the data from 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);
?>

This returns:

[
{
 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"
}
]

However I need to to appear in the following format:

[
{
 school: "School A",
 m: "2",
 f: "1"
},
{
 school: "School B",
 m: "2",
 f: "2"
}
]

I am a bit new when it come to working with Google Charts and I am open for any suggestions.

Thanks in advance!

Upvotes: 0

Views: 857

Answers (2)

R_Marlatt
R_Marlatt

Reputation: 119

I figured it out. The problem was in my SQL statement

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;

Upvotes: 0

user1517081
user1517081

Reputation: 965

Try this:

SELECT
  school,
  sum(if(gender='M',1,0)) as m,
  sum(if(gender='F',1,0)) as f
FROM
  participant
GROUP BY
  school

Upvotes: 1

Related Questions