Hi guys.
I have a DB table looking like this:
#DATE #NAME #C1 #C2
2007-08-27 user 1250 2294
2007-08-28 user 1816 3096
2007-08-29 user 89 3153
2007-08-30 user 71 2986
[..]
I started collecting data in late August 2007 and it's being updated every day.
I want to fetch the monthly totals for columns C1 and C2 and plot them in a graph (PHP/SWF charts - http://www.maani.us/charts/) but I am having troubles excluding the years/months that I have no data for.
This is what I wrote so far
$month=date("m");
$year=date("Y");
$month_array[0]="";
$c1_array[0]="C1";
$c2_array[0]="C2";
$index=1;
for ($j=2007;$j<=$year;$j++)
{
for ($i=1;$i<=12;$i++)
{
if ($i==10 || $i==11 || $i==12)
{
$query = "SELECT SUM(C1),SUM(C2) FROM stats WHERE day LIKE '".$j."-".$i."-%'";
}
else
{
$query = "SELECT SUM(C1),SUM(C2) FROM stats WHERE day LIKE '".$j."-0".$i."-%'";
}
$result = mysql_query($query);
if(mysql_num_rows($result)==0) {
break;
}
else {
$row=mysql_fetch_array($result);
$c1_array[$index]=$row[0];
$c2_array[$index]=$row[1];
$month_array[$index]=$i."-".$j;
$index++;
}
}
}
/* $index=1;
while($row = mysql_fetch_assoc($result))
{
$day_array[$index]="";
$c1_array[$index]=$row['spam'];
$c2_array[$index]=$row['ham'];
$index++;
}*/
$chart[ 'axis_ticks' ] = array ( 'value_ticks'=>true, 'category_ticks'=>true, 'major_thickness'=>2, 'minor_thickness'=>1, 'minor_count'=>1, 'major_color'=>"000000", 'minor_color'=>"222222" ,'position'=>"outside" );
$chart[ 'chart_border' ] = array ( 'color'=>"000000", 'top_thickness'=>2, 'bottom_thickness'=>2, 'left_thickness'=>2, 'right_thickness'=>2 );
$chart [ 'chart_type' ] = "line";
$chart[ 'chart_data' ] = array ($month_array, $c1_array, $c2_array);
$chart[ 'chart_grid_h' ] = array ( 'alpha'=>10, 'color'=>"000000", 'thickness'=>1, 'type'=>"solid" );
$chart[ 'chart_grid_v' ] = array ( 'alpha'=>10, 'color'=>"000000", 'thickness'=>1, 'type'=>"solid" );
$chart[ 'chart_pref' ] = array ( 'line_thickness'=>2, 'point_shape'=>"none", 'fill_shape'=>false );
SendChartData ($chart);
?>
Of course it is not working.
I believe there must be something wrong around
if(mysql_num_rows($result)==0) {
break;
}
but I don't seem to figure out how to fix it.
Thanks!