PHP/MySQL Date Grouping

Ok,

I have a table which has 3 columns - ID, Date, Value

The date is just the result of the time() function and the value column contains a number.



I want to be able to perform a query on this table and group the data in a day by day basis. So for example if there were 100 rows in this table and the date of 6 of them was on the 20th of September I would want the end result to have added up all of the numbers in the Value column to return a total for that day. The end result Im after is to have an array which contains the date as the index and then the value equal to the added amount from the Value column of that day.

Thanks

 

This will do it:

SELECT `Date`, SUM(`Value`) AS `Total`  FROM `table_name` GROUP BY `Date` ORDER BY `Date`

Code (markup):

 

Yes I can understand that working if in the Date column the format was something like: 20-09-07. However the format is instead a UNIX timestamp... e.g.: 1189485166

I need to use this format so I can also record the time in which the row was inserted as well as the date.

 

Sorry I made a mistake in my first post... The Date column is actually receiving its value from the time() function when being inserted therefore it is in the format of ##########

 

I'm still looking for help on this so if anyone can point me in the right direction that would be great

 

And I think the same problem

I have a table in my database that contains information about shows that my client's company promotes/produces (tbl). There is a second table called tbl2 that contains the details of the actual shows - date/time, location, etc. These tables are able to be joined on engagementID.

They would like to be able to run a report (in PHP page) that will return the number of shows and the money that was made on the shows for the current year, last year and two years ago based on a date entered by the user. The user specifies the date range for the report (10/1/2008 - 10/31/2008 for example) and I calculate the previous years (10/1/2007 - 10/31/2007 and 2006) using UNIX timestamps. I'm able to use this timestamp and a BETWEEN clause to search tbl2 for all shows that occur within the specified range. I then join that with tbl to get the details about the engagement. The code snippet below shows how I get the information for 10/1/2008 - 10/31/2008.

I'd like to avoid running three separate queries and put this all into one, but I'm running in to some trouble. My first question should probably be, is this the best (most efficient) way to be performing this operation?

Thank you!!

 

Hi,

I have a similar problem.

I'm learning php with mysql and designing a project for myself to learn. in my project, there are 3 persons who want to quit cigarette! they plan to smoke particular number in a week and every week they reduce the total number of cigarette.
I want to group by name and date. E.G. how many cigarette a person smokes in a day. The date format is in time() and a person maybe smoke several times in a day.

If I write what I said in SQL language, it would be something like this:

$p="select naame,sum(total),date('Y-m-d',daate) from maintest GROUP BY naame and GROUP BY date('Y-m-d',daate)" or die(mysql_error());

PHP:

But obviously it does not work (if does, I did not write this long story to waste your time )
Can anyone help me what should I write?

Thanks,

 

ok. after 2-days thinking, I guess if I write my data and my request, you can answer me:

my data:

    id,naame,datee,total
    1,he,1241665417,1
    2,he,1241208000,2
    3,she,1241664462,1
    4,he,1241294400,2
    5,she,1241635502,1
    6,he,1241380800,5
    7,he,1241467200,12
    8,he,1241634877,6
    9,she,1241634877,1
    10,she,1241633449,6
    11,she,1241208000,4
    12,she,1241294400,2
    13,she,1241380800,7
    14,she,1241467200,9
    15,he,1241669120,1
    16,she,1260576000,1
    17,she,1260576352,2
    18,she,1260576800,3
    19,she,1260576156,4

Code (markup):

My wish:

she --> 2009-05-02 --- > 4
she --> 2009-05-03 --- > 2
she --> 2009-05-04 --- > 7
she --> 2009-05-05 --- > 9
she --> 2009-05-06 --- > 10
she --> 2009-05-07 --- > 1
she --> 2009-12-12 --- > 10

Code (markup):

thanks,