doddsey_65 Posted May 5, 2011 Share Posted May 5, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/ Share on other sites More sharing options...
PFMaBiSmAd Posted May 5, 2011 Share Posted May 5, 2011 Unix timestamp (integer value) or mysql timestamp (yyyy-mm-dd value) ? Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211167 Share on other sites More sharing options...
doddsey_65 Posted May 5, 2011 Author Share Posted May 5, 2011 unix timestamp Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211169 Share on other sites More sharing options...
PFMaBiSmAd Posted May 5, 2011 Share Posted May 5, 2011 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. Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211175 Share on other sites More sharing options...
doddsey_65 Posted May 5, 2011 Author Share Posted May 5, 2011 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 Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211180 Share on other sites More sharing options...
doddsey_65 Posted May 5, 2011 Author Share Posted May 5, 2011 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(); Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211183 Share on other sites More sharing options...
PFMaBiSmAd Posted May 6, 2011 Share Posted May 6, 2011 GROUP BY DATE(FROM_UNIXTIME(p_time_posted)) Quote Link to comment https://forums.phpfreaks.com/topic/235648-sorting-by-day/#findComment-1211213 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.