CODEWITHSUNDEEP
phpmysqltimestamp
benhowdle89
benhowdle89

Reputation: 37464

MySQL PHP group by day and total for each day

I have a table in a mysql database with 3 columns: id, value and tstamp. My tstamp column is in TIMESTAMP format, ie: 2011-01-21 08:32:22

What i hope to achieve is use PHP to display a break down of a total for each day. I cant think of a way to query the table, in pseudo code: select * from table group by day(part of tstamp) and add all numbers together from the value column for each day"

Is this going to be possible?

Thanks alot

EDIT:

I have this query in combo with the accepted answer's code:

$sql = "select date(tstamp), sum(".$column.") from mash group by date(tstamp)";
           $result = mysql_query($sql);
                while($row = mysql_fetch_array($result)){
                      $strXML .= "<set name='".date("G:i:s", strtotime($row["date"])).  "' value='".$row["sum"]."' color='AFD8F8' />";
                }

How do i access the array values in $row correctly?

Upvotes: 11

Views: 13195

Answers (2)

Eiad
Eiad

Reputation: 11

How about using this

DATE_FORMAT(datestamp , '%Y') as year //for grouping over year
DATE_FORMAT(datestamp , '%Y-%m') as month //for grouping over month
DATE_FORMAT(datestamp , '%Y-%m-%d') as day //for grouping over day

Upvotes: 1

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

If you want MySQL to return you the value you are looking for in ONE QUERY you can use:

select date(tstamp), sum(value)
  from your_table
 group by date(tstamp);

Upvotes: 19

Related Questions