CODEWITHSUNDEEP
phpmysqldatedatetime
Bluemagica
Bluemagica

Reputation: 5158

PHP/mysql: how do I display records sorted by time and grouped by date?

I need to display records grouped by date, and sorted by time. In the mysql table I have a field called entry_time which has php time() values stored in it. Any ideas what would be the most compact and simplest way to do this?

Ex: I need a display like:

21st April
3:00 pm
3:50 pm

22 April
5:00 am
4:00 pm

Upvotes: 2

Views: 3796

Answers (7)

WooDzu
WooDzu

Reputation: 4866

query:

SELECT DATE_FORMAT( entry_time, '%D %M' ) as date,
       GROUP_CONCAT( DATE_FORMAT( entry_time, '%h:%i %p' )
                     ORDER BY entry_time ASC
                     SEPARATOR '<br />' ) as times
FROM yourtable
GROUP BY DATE( entry_time )
ORDER BY entry_time

results in:

> date        times

> 21st April  3:00 PM<br />3:50 PM

> 22st April  5:00 AM<br />4:00 PM

then you can go through it:

foreach($result as $row) {
   echo $row->date. '<br />'. $row->times. '<br />';
}

edit: the benefit is that all sorting & formatting is done in mysql engine, so you have a ready to display result

edit2: That query will give you exactly result you want:

SELECT CONCAT( DATE_FORMAT( entry_time, '%D %M' ), "<br />\n",
               GROUP_CONCAT( DATE_FORMAT( entry_time, '%h:%i %p' )
                             ORDER BY entry_time ASC
                             SEPARATOR '<br />' ), "<br /><br />\n"
       ) as dates
FROM yourtable
GROUP BY DATE( entry_time )
ORDER BY entry_time

use foreach (or any other array func.) to go through it:

foreach($result as $row) { echo $row->dates; }

Upvotes: 0

Guido Hendriks
Guido Hendriks

Reputation: 5776

I might be thinking way too easy, but guess it could be done with:

SELECT DATE(`datefield`) `date`, TIME(`datefield`) `time` FROM `yourtable` ORDER BY `datefield` ASC;

And then just show it with PHP. MySQL is just for fetching the data, not for displaying it. :)

Upvotes: 0

deceze
deceze

Reputation: 522500

Rough half-pseudo code:

$records = /* SELECT * FROM `records` ORDER BY `entry_time` */;

$date = null;
foreach ($records as $record) {
    $currentDate = date('Ymd', $record['entry_time']);
    if ($currentDate != $date) {
        printf('<h1>%s</h1>', date('Y-m-d', $record['entry_time']));
    }
    $date = $currentDate;

    echo date('H:i', $record['entry_time']);
}

Upvotes: 2

Tails
Tails

Reputation: 3430

If you're processing the query results with PHP, consider writing a simple query to sort your records by date:

SELECT NameOfMyThing, MyDateField FROM MyTable ORDER BY MyDateField DESC

Then use PHP to read the results:

$last_day = '';
while ($row = $query_result->fetch()) {
    $date = new DateTime($row->['MyDateField']);

    $day = $date->format('j F');     // '9 April'
    $time = $date->format('g:i a');  // '9:05 am'

    if ($day != $last_day) {
        print $day . "\n";
        $last_day = $day;
    }

    print $time . " " . $row->['NameOfMyThing'];
}

The idea here is to remember the last day you saw, and only output the date when the day changes.

(Code untested, optimized for readability only).

Upvotes: 1

smcphill
smcphill

Reputation: 826

An sql query like this (using wordpress as an example) would get you the records in the correct order, and have the correct formatting:

select date_format(post_date, "%d %b")  as day, date_format(post_date, "%T") as time from wp_posts;

You could then iterate through this array and start a new "day" heading whenever the first column changes.

I really wouldn't recommend querying the db one row at a time as suggested by @Lainlwakura's comment - PHP is slow, iterating over single row queries is slow. PHP array sorting, especially on string keys is incredible slow. If you are listing records spanning a year or so, that approach won't work.

Let mysql do as much for you as you can.

Upvotes: 0

Griwes
Griwes

Reputation: 9039

When you have time() stored in database, the easiest way is to:

  1. Sort table by column with time().
  2. Query for every record's timestamp from table (I assume that you only want dates and times).
  3. For each row in result, call date() twice: once for day and month, one for hour. Have last used date saved and if they are same, put next time to same array element as prievous (I am assuming that you use LainIwakura's solution of storing such data).
  4. Display it the way you want.

Upvotes: 0

LainIwakura
LainIwakura

Reputation: 3041

Have a multidimensional array.

$datetime = array("April 21st" => array("5:00a.m", "4:00p.m"), "April 22nd" => array(...));

Then you can sort based on date and time separately.

Upvotes: 0

Related Questions