Group By Select statement not working
Posted: Mon Feb 21, 2011 5:00 pm
Hello everyone, I am trying to pull a the sum of the total amount of calories burned by day for the past seven days, based on exercises performed by an individual. However The following would not work for me. It is returning a total (1961) that does not match what the total actually is and only one date (19th) instead of 3 dates(15th, 18th, 19th). Below is the code
Date Calories
15th 182
18th 273
19th 106
19th 500
19th 500
19th 400
19th 400
------------------------
2115 calories burned for the past 7 days
Any help would greatly be appreciated!
Date Calories
15th 182
18th 273
19th 106
19th 500
19th 500
19th 400
19th 400
------------------------
2115 calories burned for the past 7 days
Code: Select all
$id = $_GET['id'];;
$cals = array();
$dates = array();
$query = "SELECT DateEntered, SUM(CalBurned) as cals FROM indExercise WHERE MemberID= '$id' AND DateEntered >= DATE_SUB(CURDATE(), INTERVAl 7 DAY) GROUP BY DATE_FORMAT('DateEntered', '%Y %m %d') order by DateEntered DESC";
$myquery= mysql_query($query) or die('Error Occured: Select calburned from exercise. (calsburned.php)');
while($fetchInfo = mysql_fetch_array($myquery)){
$calories = $fetchInfo['cals'];
$date =$fetchInfo['DateEntered'];
$day = sprintf("%02s-%02s", substr($date,0,4),substr($date,5,6));
echo $calories."<br />";
// array_push($cals,$calories);
array_push($dates,$day);
}//end while loop
foreach($dates as $var){
echo $var."<br />";
}
for ($i=1; $i<= count($dates); $i++){
if($i > 1){
if(current($dates) == prev($dates)){
$cals[$i] += $cals[$i];
unset($cals[$i]);
unset($dates[$i]);
}
}
}