MySQL + PHP (grouping by date)

Question

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

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!;-)

Answer 1

The easiest way is probably to sort by timestamp (descending) in SQL, and do the grouping in PHP: Iterate over the result set, converting the timestamp to a DateTime object; when the current date is on a different day than the previous one (hint: use DateTime::format()), insert a sub-heading.

Answer 2

you could create a mysql udf for that, something like this:

DELIMITER $$

DROP FUNCTION IF EXISTS `niceDate` $$
CREATE FUNCTION `niceDate` (ts INT) RETURNS VARCHAR(255) NO SQL
BEGIN

  declare dt DATETIME;
  declare ret VARCHAR(255);

  set dt = FROM_UNIXTIME(ts);

  IF DATE_FORMAT(dt, "%Y%m%d") = DATE_FORMAT(NOW(), "%Y%m%d") THEN SET ret = 'Today';
  ELSEIF DATE_FORMAT(dt, "%Y%m%d") = DATE_FORMAT(DATE_ADD(NOW(), INTERVAL -1 DAY), "%Y%m%d") THEN SET ret = 'Yesterday';
  ELSE SET ret = CONCAT(DATE_FORMAT(dt, "%M "), DATE_FORMAT(dt, "%d"));
  END IF;

  RETURN ret;

END $$

DELIMITER ;

You could then construct your query like this: select niceDate(your_date_field) from table group by niceDate(your_date_field) order by your_date_field desc

disclaimer: i haven't tested this function, but you should get the idea.

Answer 3

You can convert your unix timestamp to a date using DATE(FROM_UNIXTIME(time)). This will output something like 2010-07-30.

The following should group by the date.

SELECT id, subject, time, DATE(FROM_UNIXTIME(time)) AS date_column
GROUP BY date_column

Edit: Didn't read your question correctly.

For this I would just run a standard SELECT and ORDER BY time DESC.

Then do the grouping with PHP.

$lastDate = null;

foreach ($rows as $row) {
    $date = date('Y-m-d', $row['time']);
    $time = date('H:i', $row['time']);

    if (is_null($lastDate) || $lastDate !== $date) {
        echo "<h2>{$date}</h2>";
    }

    echo "{$time}<br />";

    $lastDate = $date;
}