[SOLVED] Splitting table results by date

[dietkinnie]

i everyone,

 

I have run into a problem and need your help.

 

I am running a query and displaying the results into a table through php. This is a sample output.

 

 

      Date                      Cars

6/13/2009 6:30:00 PM    Ferrari

6/13/2009 6:30:00 PM    Subaru

6/13/2009 8:00:00 PM    Toyota

6/13/2009 8:00:00 PM    Opel

6/14/2009 5:00:00 PM    Ford

6/14/2009 6:00:00 PM    Hyundai

6/14/2009 6:00:00 PM    Fiat

6/15/2009 9:00:00 PM    BMW

 

However i would now like to split and group the results up by date to something like this

 

 

 

      Date                      Cars        Sum(cars)

6/13/2009 6:30:00 PM    Ferrari      76

6/13/2009 6:30:00 PM    Subaru      67

6/13/2009 8:00:00 PM    Toyota      6

6/13/2009 8:00:00 PM    Opel          98

--New Table

6/14/2009 5:00:00 PM    Ford          65

6/14/2009 6:00:00 PM    Hyundai    65

6/14/2009 6:00:00 PM    Fiat          65

--New Table

6/15/2009 9:00:00 PM    BMW        4

 

Any input would be greatly appreciated as i have run out of ideas  ???

 

Thanks in advance!!

[joel24]

do a

"SELECT * FROM whateverTable ORDER BY DATE(date) DESC" (or ASC depending on what you want)

 

then in the while loop that echos the data have something like

$lastDate = "";
while($cars = mysql_fetch_array($carSQL)) {
$date = $cars['date'];
*** put in code to split date from time... using a date function or something) ***

if ($date != $lastDate) {
echo "<tr><td>Next Date Cars</td></tr>";
}

echo "<tr><td>$cars['date']</td></tr>";
echo <tr><td>$cars['car']</td></tr>";

$lastDate = $date;
}

 

that splits up a single table and puts a new row in tho... you wanted new tables...

you could have it start with a <table> in the html at the top..

then change echo "<tr><td>Next Date Cars</td></tr>"; to echo "</table><table>";

 

and after the while loop have a </table> in the html.

[waynew]

Could you tell us what name your table is and the names of its columns?

[joel24]

let me get this right,

you just want to group them into the dates and you have all the data in your columns?

 

or do you have a table that has say 200 cars, maybe 15 ferrari's in it, and you want to list each distinct car brand and count how many types of each car there is?

[waynew]

I was feeling charitable:

 

<?php

//you'll need to change this for your table
$result = mysql_query("SELECT car_type, date FROM cars ORDER BY date DESC") or trigger_error(mysql_error());

//if query went ok - i.e. didnt cough up an error or anything
if($result){
   
   //lastDate is NULL by default
   $lastDate = NULL;
   
   //begin table
   echo '<table>';
   echo '<tr><th>Date</th><th>Cars</th></tr>';
   
   //loop through result
   while($cars = mysql_fetch_assoc($result)) {
      
      //get d/m/y date from datetime
      $date = date("d/m/Y",strtotime($cars['date']));
      
      //if this date not equal to last date - obviously its a new date
      if ($date != $lastDate) {
         echo '<tr><td colspan="2">Next Date Cars</td></tr>';
      }
      
      echo '<tr><td>'.$date.'</td><td>'.htmlentities($cars['car'],ENT_QUOTES,"utf-8").'</td></tr>';
      $lastDate = $date;
   }
   echo '</table>';
}
?>

[dietkinnie]

Hi joel24 and  waynewex  thanks for your replies , it was so simple, i was tearing my hair out over this.

 

Problem solved now.

 

 

Great stuff , thanks again guys !!