Page 1 of 1

How to find when month (in database) changes

Posted: Mon Jun 27, 2005 3:29 am
by robster
Hi all,

I must be very tired today, I'm having trouble with this one.

I have a whole heap of transactions in a database. Each transaction has a MSQL Date recorded in it. Date format is 0000-00-00. A typical date recorded in the database is 2004-12-24 (yyyy-mm-dd).

I have created a list of transactions that, whilst drawing the table containing transactions, will create a subtotal at the end of each day. This works really nicely and is done when the whole date changes.

What I want to do now, is do the same, but with the month. When the month changes, I will create a monthly subtotal (then yearly etc).


So, the question is (finally ;)). How can I define when the month has changed? Do I do it with a string comparison, can the date function do it (by feeding the databases date into the date function for example?).

Take note there is a loop that parses through the transactions table, and draws each transaction in a linear fashion, so it's very easy to compare one transaction with its previous one.


Any advice would be appreciated...

Rob

Posted: Mon Jun 27, 2005 4:14 am
by Chris Corbyn
Slightly confused but I think GROUP BY could help you here.

Select the MONTH() with an alias (e.g. X) from the date field and then GROUP BY X.

The rest of your query can remain unchaged, it'll just cluster all the months together that way and perform the operations on each group (each month) ;)

...

Posted: Mon Jun 27, 2005 5:16 am
by s.dot
I recently learned this from someone on the forums.

You most definately should do what the guy above me said.

And for displaying it yearly something like this would work quite nicely

Code: Select all

<?php
$query = mysql_query("SELECT field, month FROM table WHERE month IN ('01','02',03','04',05','06','07','08','09','10','11','12') GROUP BY month ORDER BY month ASC");

$current_month = null;
while($res = mysql_fetch_assoc($query))
{
     if($current_month != $res['month']
     {
          // new heading like January 2005, February 2005, etc.
     }
     // Process your data
     $current_month = $res['month'];
} ?>
Eh I don't even know why I posted that. I guess I figured if you were having trouble with months, you might have trouble with the year too. Sorry :(

:)