Grouping records by day in PHP/MySQL

Question

This is a common issue that I can't find an elegant way to handle.

Database contains 5000 records. I need to show these records on a page, but they need to be sorted and grouped by day.

10/11/2009

record3456

record456

record456

10/12/2009

record345234

record3456

10/13/2009

10/14/2009

record81

record8324

record983

record834

10/15/2009

record918

...

etc. How can this be done in MySQL/PHP? What about Ruby? I can grab all records and then sort them and manipulate them manually with PHP but that's SLOW.

Answer 1

Do a:

ORDER BY your_date_column

in the query. You'll probably have to do the grouping in PHP - just see if the data differs from the last row, if it does, print a header.

Answer 2

Hello,

SELECT *

FROM TABLE GROUP BY DAY(dateField)

ORDER BY DAY(dateField)

http://www.tizag.com/sqlTutorial/sqldate.php

Answer 3

Assuming the example for 10/11/2009 is incorrect because of the duplicates, this is a standard pivot query. It's called pivotting because you're wanting to change row to column data.

SELECT CASE WHEN x.df = '10/11/2009' THEN x.record_column ELSE NULL END '10/11/2009',
       CASE WHEN x.df = '10/12/2009' THEN x.record_column ELSE NULL END '10/12/2009',
       CASE WHEN x.df = '10/13/2009' THEN x.record_column ELSE NULL END '10/13/2009',
  FROM (SELECT t.date_column,
               DATE_FORMAT(t.date_column, '%m/%d/%Y') 'df'
               t.record_column
          FROM TABLE t
      GROUP BY t.date_column,t.record_column) x

The problem with this is that your resultset will look like:

10/11/2009  | 10/12/2009    | 10/13/2009
-----------------------------------------
record3456  | NULL          | NULL
record456   | NULL          | NULL
NULL        | record345234  | NULL
NULL        | record3456    | NULL

It might be more simple & direct to just use:

SELECT t.record_column '10/11/2009'
  FROM TABLE t
 WHERE t.date_column = '10/11/2009'

...to query the records you want, in separate queries.