CODEWITHSUNDEEP
phpmysqltimestamp
ItsJamie
ItsJamie

Reputation: 261

MySQL PHP group results by date and echo them

My MySQL Table

id, message, date

date is a timestamp

I would like it to be echoed in PHP like this:

Tuesday 20th August 2013 
- message 3
- message 2

Monday 19th August 2013 
- message 1

I have no idea how I would group the results and echo them for each day

Upvotes: 1

Views: 3020

Answers (3)

Roseann Solano
Roseann Solano

Reputation: 768

<?php
$query = mysql_query("SELECT id,message FROM messages ORDER BY date DESC");
$data = array();
while($row = mysql_fetch_assoc($query)){
    $date = date("F j,Y",strtotime($row['date']));
    if(array_key_exists($date,$data)){
        $data[$date][] = array(
            'id'      => $row['id'],
            'message' => $row['message'],
        );
    }else{
        array_push($data,$data[$date]);
        $data[$date][] = array(
            'id'      => $row['id'],
            'message' => $row['message'],
        );
    }
}
echo '<pre>';
print_r($data);
?>

Upvotes: 0

dkkumargoyal
dkkumargoyal

Reputation: 556

SQL Query

select group_concat(message), date from table group by date order by date

Now in code you get equal number of rows as dates and for each date messages as comma separate strings for example (date will be in timestamp - you can format that in code or mysql query)

20th Aug 2013 | message 3,message 1

19th August 2013 | message 2,message 4

Loop through each record and print date and then split the messages with , (comma) and print

Upvotes: 2

Shemeer M Ali
Shemeer M Ali

Reputation: 1041

SQL Query

SELECT id,message FROM messages ORDER BY date DESC

PHP side

You have write a loop to print messages and write condition to "date" should not repeat again.

Upvotes: 0

Related Questions