Hi everybody,
I'm trying to do something relatively simple here. Basically I have a table with a bunch of rows in it marked with a timestamp (format: 2009-05-30 00:14:57).
What I'm wanting to is do is a query which pulls out all of the rows, and splits them by the month so I'm left with a final result like:
February
rowID name order date
rowID name order date
rowID name order date
January
rowID name order date
rowID name order date
rowID name order date
etc.
I have a few vague ideas how to do this - they just seem long winded.
One of the ways would be to do a query for each month. I'd derive what the current month is in PHP then construct a for() which goes back a certain number of months.
like:
$currentmonth = 8;
$last6months = $currentmonth - 6;
for($i = $currentmonth; $i == $last6months; $i--) {
$sql = 'SELECT * FROM reports WHERE MONTH(reports.when) = $currentmonth ';
$res = mysql_query($sql);
// something would go here to convert the month numeral into a month name $currentmonthname
echo $currentmonthname;
while($row = mysql_fetch_array($res)) {
// print out the rows for this month here
}
}
Is there a better way to do this?