CODEWITHSUNDEEP
phpmysqldate
EibergDK
EibergDK

Reputation: 564

Listing dates found in mySQL database using PHP

Let us say that this is my table in my database:

ID      | APP_TIME                  | NAME
----------------------------------------------------
1       | 2012-07-08 10:00:00       | John
2       | 2012-07-08 12:00:00       | Johnny
3       | 2012-07-09 13:00:00       | Fred
4       | 2012-07-10 09:00:00       | George
5       | 2012-07-10 10:30:00       | Eva
6       | 2012-07-10 14:00:00       | Monica
7       | 2012-07-11 12:00:00       | Helen
8       | 2012-07-11 13:00:00       | Kim

What I then want to output at my site:

07-08-2012
Time    | Name
----------------------------------------------------
10:00   | John
12:00   | Johnny


07-09-2012
Time    | Name
----------------------------------------------------
13:00   | Fred


07-10-2012
Time    | Name
----------------------------------------------------
09:00   | George
10:30   | Eva
14:00   | Monica


07-11-2012
Time    | Name
----------------------------------------------------
12:00   | Helen
13:00   | Kim

My problem is not formatting the datetime values and all that... The challenge for me is to list the results accordingly to the dates they "belong" to.

Someone help ? :)

Upvotes: 0

Views: 86

Answers (2)

EibergDK
EibergDK

Reputation: 564

Came up with this solution:

$today = date('%Y-%m-%d 00:00:00');
$get_dates = mysql_query("SELECT DISTINCT DATE_FORMAT(app_time, '%Y-%m-%d') AS new_date FROM reservations WHERE app_time > '$today' ORDER BY app_time ASC");
while ($row = mysql_fetch_array($get_dates)) {
    $the_date = $row['new_date'];
    $results = mysql_query("SELECT * FROM reservations WHERE app_time >= '$the_date 00:00:00' AND app_time < '$the_date 23:59:59' ORDER BY app_time ASC");
    while ($the_results = mysql_fetch_array($results))
    {
    // Do some stuff eg:
    echo $the_results['name'] . '<br />';
    }
}

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

Let MySQL do the hard work for you like this:

SELECT DATE(app_time) as `day`, TIME(app_time) as `time`, name FROM table ORDER BY `day` ASC, `time` ASC

This will return the date, time of day, and name sorted by date then time of day.

You then loop the result rows and aggregate into whatever structure you like the example below (this assumes you already have a result set from your query set as $result and you are using mysqli)

$sorted_array = array();
while ($row = mysqli_fetch_object($result)) {
    $sorted_array[$row->day][] = array('time' => $row->time, 'name' => $row->name);
}

Upvotes: 1

Related Questions