MySQL + PHP (grouping by date)

[ethar]

I'm trying to output a list of different itmes grouped by the date they were stored in the database

I'd need help with both MySQL (query) and PHP (output).

MySQL table

id | subject | time
1 | test1 | 1280278800
2 | test2 | 1280278800
3 | test3 | 1280365200
4 | test4 | 1280451600
5 | test5 | 1280451600

OUTPUT

Today
test5
test4

Yesterday
test3

July 28
test2
test1

I'd appreciate any help on this. Thanks!

[requinix]

Sort by the time descending and change your headings in code when the corresponding date changes.

 

$date = empty
foreach ($row found) {
	if (date from $row != $date) {
		$date = date from $row
		echo new date heading
	}
	echo item
}

If your dates are grouped using something that needs beginning and ending output (like a beginning and ending ) then use a slightly more complicated

$date = empty
foreach ($row found) {
	if (date from $row != $date) {
		if ($date is not empty) {
			echo ending output
		}
		$date = date from $row
		echo new date heading and beginning output
	}
	echo item
}
if ($date is not empty) {
	echo ending output
}

Edited June 5, 2013 by requinix