Change Query For Google Chart Data

[shaunie]

Hi,

 

I have created a table to get a Google Chart working, which works on aggregated data:

 

CREATE TABLE `chart_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `month` varchar(50) NOT NULL,
  `location` varchar(50) NOT NULL,
  `open` int(7) NOT NULL,
  `closed` int(7) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
);

 

With the following data examples:

 

INSERT INTO `chart_data` VALUES 
(1,'January','Paris',308,500),
(2,'February','Paris',257,420),
(3,'March','Paris',375,235),
(4,'April','Paris',123,387),
(5,'May','Paris',308,500),
(6,'June','Paris',257,420),
(7,'July','Paris',375,235),
(8,'August','Paris',123,387),
(9,'September','Paris',308,500),
(10,'October','Paris',257,420),
(11,'November','Paris',375,235),
(12,'December','Paris',123,387),
(13,'January','',108,100),
(14,'February','New York',257,420),
(15,'March','New York',675,235),
(16,'April','New York',623,317),
(17,'May','New York',108,100),
(18,'June','New York',157,220),
(19,'July','New York',475,435),
(20,'August','New York',523,387),
(21,'September','New York',108,100),
(22,'October','New York',157,220),
(23,'November','New York',575,435),
(24,'December','Paris',323,487);

 

However the actual data table that I need to move it to doesn't have aggregated figures for open and closed events, just an entry for each event with a date, location and a status field. How can I query this table to populate the array in the same way?

 

Here is my PHP code:

 

<?php

mysql_connect('localhost','root','');
mysql_select_db('chart');

$sqlquery1="SELECT month, location, open, closed FROM chart_data)";

$sqlresult1=mysql_query($sqlquery1);

$table=array();
$table['cols']=array(
        array('label'=>'Month', type=>'string'),
	array('label'=>'Location', type=>'string'),
        array('label'=>'Open', type=>'number'),
        array('label'=>'Closed', type=>'number'),
);
$rows=array();

while($r=mysql_fetch_assoc($sqlresult1)){
        $temp=array();
        $temp[]=array('v' => $r['month']);
        $temp[]=array('v' => $r['location']);
        $temp[]=array('v' =>(int) $r['open']);
        $temp[]=array('v' =>(int) $r['closed']);

        $rows[]=array('c' => $temp);
}

$table['rows']=$rows;

$jsonTable = json_encode($table);
//this print statement just for testing
print $jsonTable;

?>

Thank you for your advice

 

[Barand]

$sqlquery1="SELECT month, location, SUM(open) as open, SUM(closed) as closed

           FROM chart_data

           GROUP BY month, location"

[shaunie]

Thank you for your reply but the actual data table does not have open and closed fields. Just an entry for each event with a date, location and status field. How can I query the data when it is structured like this? I think maybe it should query the same table twice as I need to COUNT each open and closed event, but I am not sure how to do this?

[Jessica]

I'm going to guess you'll need to join the table to itself. If you show us the actual table structure you're working with, we might actually be able to help

[shaunie]

Apologies, here is the new table code:

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime NOT NULL,
  `location` varchar(50) NOT NULL,
  `status` int(7) NOT NULL
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
);

[Jessica]

And some data.