sorting by day

[doddsey_65]

i need to sort my database results by the day they were added to the database. There is a column with the timestamp of when they were inserted but how do i group them by the day they were inserted so i can display them as such:

 

Mon 14th: 5 records

Tues 15th: 11 records

 

etc.

 

[PFMaBiSmAd]

Unix timestamp (integer value) or mysql timestamp (yyyy-mm-dd value) ?

[doddsey_65]

unix timestamp

[PFMaBiSmAd]

You will need to use the mysql FROM_UNIXTIME() function in your query to get the values into a yyyy-mm-dd value. From there, you can GROUP BY the part(s) of the date you want and use COUNT() to get a count of the number of records in each group.

 

Unix timestamps are the worst way to store date/time information that you need to manipulate based on calendar divisions. 

 

[doddsey_65]

i realise now that this was a bad way to save times but i never intended to use them this way so i didnt think it mattered. Thanks for the advice

 

 

[doddsey_65]

am i on the right lines with this:

 

$query = $link->query("SELECT *
                    FROM ".TBL_PREFIX."posts
                    GROUP BY FROM_UNIXTIME(p_time_posted)
                        ");
$row = $query->fetchAll();

 

[PFMaBiSmAd]

GROUP BY DATE(FROM_UNIXTIME(p_time_posted))