amCharts: Is it possible to 'add up total' rows with matching date together?

I want to use the chart (http://www.amcharts.com) to display 'Last 7 Days' Total Impressions & Clicks in the advertising php script Admin Area.

My problem is I have rows like this:

id 	ad_id 	date 	 	exp    clicks
1 	2 	2011-02-07 	1 	1
2 	5 	2011-02-07 	12 	5
3 	2 	2011-02-20 	3 	0
4 	11 	2011-02-21 	1 	0
5 	2 	2011-03-02 	89 	0
6 	18 	2011-03-02 	43 	0
7 	19 	2011-03-02 	60 	0
8 	19 	2011-03-02 	1 	0

Code (markup):

So somehow I want to get data like this:

2011-02-07 	13 	6
2011-02-20 	3 	0
2011-02-21 	1 	0
2011-03-02 	193 	0

Code (markup):

<?php 
include ('../../../includes/connect.php');


$query2 = "SELECT * FROM data";
$result = mysql_query($query2) or die('Error, query failed : ' . mysql_error()); 
while($row = mysql_fetch_assoc($result)) {
   $data[] = $row;
}
$data[0]['date'];
$data[1]['date'];
$data[2]['date'];
$data[3]['date'];
$data[4]['date'];
$data[5]['date'];
$data[6]['date'];

$data[0]['exp'];
$data[1]['exp'];
$data[2]['exp'];
$data[3]['exp'];
$data[4]['exp'];
$data[5]['exp'];
$data[6]['exp'];

$data[0]['clicks'];
$data[1]['clicks'];
$data[2]['clicks'];
$data[3]['clicks'];
$data[4]['clicks'];
$data[5]['clicks'];
$data[6]['clicks'];


echo '<'.'?xml version="1.0" encoding="UTF-8"?'.'>
';  
?>
<chart>
<? 
echo '<series>
';
echo '<value xid="0">'.$data[0]['date'].'</value>
'; 
echo '<value xid="1">'.$data[1]['date'].'</value>
'; 
echo '<value xid="2">'.$data[2]['date'].'</value>
'; 
echo '<value xid="3">'.$data[3]['date'].'</value>
'; 
echo '<value xid="4">'.$data[4]['date'].'</value>
'; 
echo '<value xid="5">'.$data[5]['date'].'</value>
'; 
echo '<value xid="6">'.$data[6]['date'].'</value>
'; 
echo '</series>
';
?>
	<graphs>
<?
echo '<graph title="Clicks" fill_alpha="60" line_width="2" bullet="round" color="#FF9E01">
';
echo '<value xid="0">'.$data[0]["clicks"].'</value>
'; 
echo '<value xid="1">'.$data[1]["clicks"].'</value>
'; 
echo '<value xid="2">'.$data[2]["clicks"].'</value>
'; 
echo '<value xid="3">'.$data[3]["clicks"].'</value>
'; 
echo '<value xid="4">'.$data[4]["clicks"].'</value>
'; 
echo '<value xid="5">'.$data[5]["clicks"].'</value>
'; 
echo '<value xid="6">'.$data[6]["clicks"].'</value>
'; 
echo '</graph>
';

echo '<graph title="Impressions" fill_alpha="60" line_width="2" bullet="round" color="#0D8ECF">
';
echo '<value xid="0">'.$data[0]["exp"].'</value>
'; 
echo '<value xid="1">'.$data[1]["exp"].'</value>
'; 
echo '<value xid="2">'.$data[2]["exp"].'</value>
'; 
echo '<value xid="3">'.$data[3]["exp"].'</value>
'; 
echo '<value xid="4">'.$data[4]["exp"].'</value>
'; 
echo '<value xid="5">'.$data[5]["exp"].'</value>
'; 
echo '<value xid="6">'.$data[6]["exp"].'</value>
'; 
echo '</graph>
';

?>
	</graphs>
</chart>

PHP:

 

You need to aggregate your query with the Group By clause.

$query2 = "SELECT date, SUM(exp) AS totexp, SUM(clicks) AS totclicks FROM data GROUP BY date;";

 

Oh its that simple. Awesome.