Get statistics by day - datetime column - mysql/php

Question

I have an issue that I can't solve by myself. The best thing would be if it's possible to do it directly from mysql but if not, calculating from php is also good.

I have a table that has 3 columns: id, time, value

Now the time value is datetime (example: 2012-11-03 00:13:12)

I want to create statistics from the last 7 days. Let's say I have 7 rows and today is 2012-12-20.

Here are the rows: datetime value
2012-12-20 00:12:12 2
2012-12-20 04:06:15 4
2012-12-20 03:12:14 5
2012-12-18 03:12:14 7
2012-12-16 12:12:14 3
2012-12-16 14:23:34 2
2012-12-15 14:23:34 9

If I want to get the last 7 days report, it would be this:

2012-12-14 -> value =0
2012-12-15 -> value =9

2012-12-16 -> value =5

2012-12-17 -> value =0

2012-12-18 -> value =7
2012-12-19 -> value =0

2012-12-20 -> value =11

Currently I have this (extract last 7 day records):

$date=date("Y-m-d");
$date=date ("Y-m-d", strtotime ( '-7 day' . $date ) );  
$qry="select value,date from mytable where date>'$date'";
$res=mysql_query($qry) or die("Couldn't select Information : ".mysql_error());
while($row=mysql_fetch_array($res))
    {
        echo $row["value"];
    }

I hope somebody can help with advice. Thanks in advance! Vlad

Answer 1

This should be relatively easy to solve using just MySQL. This is very similar to this question: MySQL Query GROUP BY day / month / year

You need to change your query so it gets all the records for a given week.

SELECT DAY(date) , SUM(value)
FROM mytable
WHERE WEEK(date) = WEEK(NOW())
GROUP BY DAY(date)

Its also very easy to change how you group the numbers, for example by month using GROUP BY MONTH(date). You could also make the MySQL display the day name as a field by doing SELECT DAYNAME(date) For more details on what functions are available check out the MySQL User Guide on Date and Time functions.