MySQL + PHP (grouping by date)
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 | 1280451600OUTPUT
Today
test5 test4Yesterday
test3July 28
test2 test1I'd appreciate any help on this. Thanks!;-)
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;
}
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.
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.
精彩评论