Group by date

[doddsey_65]

im building an alert system where all alerts for the current user are listed. What i would like though is for the alerts to be grouped under the date they were added to the database.

 

For example this is how it displays now, its just a list:

 

alert1
alert2
alert3

 

but i would like it like this:

 

29th July
alert1
alert2

28th July
alert3
alert4
alert5

 

Anyone have any idea how i may go about it? I have a date column(UNIX timestamp) in the database for the alerts but it wouldnt be grouping using the SQL would it?

 

[WebStyles]

is your timestamp the number of seconds since Jan 1, 1970 (php style) or a mysql timestamp similar to this: 2007-12-15 23:50:26 ?

[PFMaBiSmAd]

Unfortunately Unix Timestamps are the worst way of storing data that you need to manipulate using human calendar units (days, weeks, months, years) because you must perform a (relatively slow) conversion on all the data to get it into a format where you can perform the needed manipulation.

 

If you use the mysql FROM_UNIXTIME(unix_timestamp,format) function in your query to convert and select the date in the format you want, you can then detect in your php code when the date changes and output a new date heading followed by the values under each date.

 

 

 

[doddsey_65]

Thanks for the comments, unfortunetly i have used the UNIX timestamp for every database date, so i will look into that sql method. Thanks