Add up table values from specific date

[thaidomizil]

Hi there,

 

i'm trying to sum up values from mysql based on their date, specifically only values from entries with the same date should be added together, my db looks like this:

 

id, codes, value, date

 

now i'm building a statistics page, the value field is always 50, for showing the total for all the entries i'm just doing

$totalincome = $codes * 50;

and print that on the statistic, i want to have statistics for each day, the date is saved in this format: YYYY-MM-DD

 

how could i do that ?

 

Thanks !

[creata.physics]

Okay, that doesn't seem very hard to do at all.

 

Fist off, we need to get an idea of what you want, which you provided well enough, so let's figure out how we want to do what is needed to be done.

 

Well you've already said the date format is YYYY-MM-DD, and you're wanting to group by that date.

 

While mysql is a logical route to take for performing this query, I'm not sure how accurate it'd be.  We can use a different friendly method to solve what you want.

 

First off, you can make a query to grab all the rows of data you are trying to compare the statistics of.

 

<?php
$query = mysql_query("select * from database_table");
while ( $row = mysql_fetch_assoc( $query ) )
{
    $_data[ $row['date'] ][] = $row;
}

 

With the code above, we'll have created an array that contains all of your results for each date.

 

Let's say you have two results for today, 2011-10-16, your array will look something like:

 

[2011-10-16]

[0] => #first row

[1] => #second row

 

So now we have an array that contains each row grouped by date.

 

Now to produce results for a specific date using $_GET, you can do:

<?php
foreach ( $_data[ $_GET['date'] as $key => $value )
{
   echo $value['value'];
}

 

So if you use, www.example.com/?date=2011-10-16

 

You'll have outputted all results for that specific date only, I'm not sure how you wanted to get those specific dated results but I'm sure you can figure out what to do from then.

 

If you have any additional questions feel free to ask.

 

Thanks.