title: Get statistics by day - datetime column - mysql/php
body: <p>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.</p>
  
  <p>I have a table that has 3 columns: id, time, value</p>
  
  <p>Now the time value is datetime (example: 2012-11-03 00:13:12)</p>
  
  <p>I want to create statistics from the last 7 days. Let's say I have 7 rows and today is 2012-12-20.</p>
  
  <p>Here are the rows:
  datetime                   value<BR>
  2012-12-20 00:12:12      2<BR>
  2012-12-20 04:06:15      4<BR>
  2012-12-20 03:12:14      5<BR>
  2012-12-18 03:12:14      7<BR>
  2012-12-16 12:12:14      3<BR>
  2012-12-16 14:23:34      2<BR>
  2012-12-15 14:23:34      9<BR></p>
  
  <p>If I want to get the last 7 days report, it would be this:</p>
  
  <blockquote>
    <p>2012-12-14 -> value =0<br>
    2012-12-15 -> value =9 </p>
    
    <p>2012-12-16 -> value =5</p>
    
    <p>2012-12-17 -> value =0  </p>
    
    <p>2012-12-18 -> value =7<br>
    2012-12-19 -> value =0</p>
    
    <p>2012-12-20 -> value =11</p>
  </blockquote>
  
  <p>Currently I have this (extract last 7 day records):</p>
  
  <pre><code>$date=date("Y-m-d");
  $date=date ("Y-m-d", strtotime ( '-7 day' . $date ) );  
  $qry="select value,date from mytable where date&gt;'$date'";
  $res=mysql_query($qry) or die("Couldn't select Information : ".mysql_error());
  while($row=mysql_fetch_array($res))
      {
          echo $row["value"];
      }
  </code></pre>
  
  <p>I hope somebody can help with advice.
   Thanks in advance!
   Vlad</p>