Mysql get records or data by monthly

[guest2478]

Hi there,

Is there a way to write a MySQL query to get records by monthly for example:

Starting from the current month:

----- August -----
Record 1
Record 2

----- July-----
Record 1
Record 2


----- June -----
Record 1
Record 2


I have a time stamp column in my db table.

I can get all the records for the current month, using the month function, for a example:

SELECT name, id, dob FROM users WHERE MONTH(regd) = MONTH(CURDATE())

thanks...

[The Little Guy]

Like this:

select name, id, dob from users order by month(regdate);

[guest2478]

thanks 'The Little Guy' ,

one more thing i forgot to mention is i wanted to output this in this format.

Do i create separate query for each month and then store each month in a array and parse each array to display them.

(note im using PHP, array will be outputted using a foreach loop )



thanks again

[The Little Guy]

You might be able to do something like this:

(untested)

<?php
$sql = mysql_query("select name, id, dob, month(regdate) as cmonth from users order by month(regdate)");
$array = array();
$month = 0;
while($row = mysql_fetch_assoc($sql)){
    if($row['cmonth'] != $month){
        $month = $row['cmonth'];
        $time = mktime(0,0,0,$month);
        echo "--- ".date("F", $time)." ---<br />";
    }
    echo "{$row['id']}, {$row['name']}, {$row['dob']}<br />";
}
?>