Page 1 of 1

unsure how to group data from same day. MySQL

Posted: Sat May 17, 2003 12:52 am
by Sevengraff
Using MySQL.
Allright, I'm making a news script, and I want to group items that are posted on the same day. I normally just do a simple sql query like SELECT * FROM news ORDER BY date DESC LIMIT 0, 15, and then have a loop to print it out. Right now I use a unix timestamp as the date, but since i'm not done with the script, I don't care if I would have to change that.

Basically, I'm trying to get something like at livejournal.com. Where if two items are posted on the same day, then they are posted together. I don't know how to determine if something should be grouped or not.

Posted: Sat May 17, 2003 3:49 am
by jollyjumper
Hi Sevengraff,

I would do it something like this:

Code: Select all

$sql = "select * from news order by date desc limit 0,15";
$query = mysql_query($sql);
$previousdate = -1 //a date with value -1 will never exist
while ($record = mysql_fetch_array($query)) {
   //check if the date of the current record differs from the previous date.
   if ($recordї"date"] != $previousdate) {
     //check to see if the previousdate is the default value set before the looping of the records, 
//to see if it's the first time we've entered the loop, so it doesn't need the ending of the group.
     if ($previousdate != -1) {
      //place the code here for the ending of the group, eg:
        echo "</table><br>";
      //end of the end group code.
     &#125;

     //place the code here for the beginning of the group, eg:
       echo "<table>";
     //end of begingroup code
     $previousdate = $record&#1111;"date"];
   &#125;
   //place the code per record here, eg:
     echo "<tr><td>" . $record&#1111;"title"] . "</td><td>" . $record&#1111;"date"] . "</td></tr>";
   //end of code per record
&#125;
  //place the same end group code here as you put within the loop
  echo "</table><br>";
Note: There could be some parse errors in this script as I typed it directly in this box and don't have the possibility to check it right now.

I hope this helps you.

Greetz Jolly.