How to find when month (in database) changes

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
robster
Forum Contributor
Posts: 360
Joined: Wed Jul 16, 2003 8:28 am
Location: Sunshine Coast, Australia

How to find when month (in database) changes

Post 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
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post 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) ;)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

...

Post 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 :(

:)
Post Reply