[SOLVED] Grouping mysql query result by dates

[esport]

Hi Guys,   

I have a mysql query result that displays a set of news heading thats are sorted by the date that it was created. I was wondering how to group them so it produces the following layout.

     

  Today 

  News Item   

  News Item   

  News Item 

  Yesterday 

  News Item   

  News Item   

  Later 

  News Item   

  News Item   

  News Item         

       

  Thanks     

  Daniel

[nuxy]

Hi there esport,

 

I would have to suggest that you firstly calculate when is "Today", "Yesterday", "Later" and then add the results in an array.

Here is a very vague example of what I mean.

<?php
$query = mysql_query('SELECT date, id FROM news');
while ($row = mysql_fetch_assoc($query)) {
     if ($row['date'] <= ($row['date']-86400)) $today[] = $row['id'];
     else if ($row['date'] <= ($row['date']-172800)) $yesterday[] = $row['id']; 
     else $later[] = $row['id']; 
}

echo '<b>Today</b>';
foreach($today as $id) {
     $query = mysql_query("SELECT title, description FROM news WHERE id = '$id'");
     $info = mysql_fetch_assoc($query);
     echo $info['title'] . '<br><i>' . $info['description'] . '</i><br>';
}

echo '<b>Yesterday</b>';
foreach($yesterday as $id) {
     $query = mysql_query("SELECT title, description FROM news WHERE id = '$id'");
     $info = mysql_fetch_assoc($query);
     echo $info['title'] . '<br><i>' . $info['description'] . '</i><br>';
}

echo '<b>Later</b>';
foreach($later as $id) {
     $query = mysql_query("SELECT title, description FROM news WHERE id = '$id'");
     $info = mysql_fetch_assoc($query);
     echo $info['title'] . '<br><i>' . $info['description'] . '</i><br>';
}

?>

 

Hope that gives you an idea..

[aschk]

What you have there is a 3D form of the data you're trying to represent.

MySQL won't allow you to insert "Today", "Yesterday", "Later" into the resultset like you want.

What you can do is ORDER BY <dateColumn> in your SQL, and then have PHP parse through them checking whether the date is "Today", "Yesterday" or "Later".

SQL:

SELECT id, 
           CASE
            WHEN '2007-11-11 14:23:11' >= CURRENT_DATE THEN 'Today'
            WHEN '2007-11-11 14:23:11' BETWEEN SUBDATE(CURRENT_DATE, INTERVAL 1 DAY) AND CURRENT_DATE THEN 'Yesterday'
            WHEN '2007-11-11 14:23:11' < SUBDATE(CURRENT_DATE, INTERVAL 1 DAY) THEN 'Later'
            ELSE 'Unknown Date'
           END as 'When'
FROM news
ORDER BY date DESC

[obsidian]

As far as grouping them into an array you can output like you are after, just do something like this:

<?php
$rows = array();
while ($row = mysql_fetch_assoc($sql))
{
  $rows[$row['date']][] = $row;
}

foreach ($rows as $date => $records)
{
  echo "<h2>$date</h2>\n";
  foreach ($records as $r)
  {
    // Output your individual records here.
  }
}
?>

[aschk]

MySQL pivot table anyone? I'm considering, given the data, whether it is a viable option...

[obsidian]

MySQL pivot table anyone? I'm considering, given the data, whether it is a viable option...

 

Personally, I would think that may be a bit overkill. If it's going to be queried over and over again, might be a viable solution, though.

[esport]

Thanks guys, problem now solved. Great team effort. I used the mysql from aschk

and grouped them using the code from obsidian.  Im just a little confused how this array works.

 

             

     $rows[$row['date']][] = $row;

         

  It somehow groups the items from the query??

         

Thanks heaps and much appreciated.

  Daniel 

[obsidian]

Thanks guys, problem now solved. Great team effort. I used the mysql from aschk

and grouped them using the code from obsidian.  Im just a little confused how this array works.

 

             

     $rows[$row['date']][] = $row;

         

  It somehow groups the items from the query??

         

Thanks heaps and much appreciated.

  Daniel 

 

The code creates a multidimensional array. The first tier of your array is indexed by the date column (which groups subsets into those date fields). The empty brackets ("[]") simply tack on an additional row to that date, similar to using array_push(). Hope that helps.

[esport]

Thanks mate for the explaination. I think it was the empty "[]" that confused me. I didn't relise it acted as the same as array_push.