Grouping Results from SQL Query

Hey everyone, have an interesting program I am trying to figure out in my mind as far as how I would code this.

I wrote a tee time script from scratch and have support for outings and leagues at the golf course. On my outings page, anything with "Outing:" in the "name" field of the sql database is displayed on the bottom of the page. With so many outings that the course has, this list is HUGE.

Anyways, for a single outing with 8 tee times, it shows up like this:

That is the direct result from the query. The code I am using looks like this:

 <?php 
include 'includes/sqlconnect.php';
$name = 'outing';    
$query = "SELECT *
FROM `data`
WHERE `name` LIKE CONVERT( _utf8 '%$name%'
USING latin1 )

LIMIT 0 , 10000 "; 
$result = mysql_query($query) or die("Query failed ($query) - " . mysql_error()); 
if(mysql_num_rows($result)) 
{ 
   while($row = mysql_fetch_assoc($result)) 
   { 
      $data = $row['name'] ; 
	  $date = $row['date'] ; 
	  $data3 = $row['time'] ; 
	  $month = substr($date, 0, 2);
      $day = substr($date, 2, 2);
      $year = substr($date, 4, 4);
      $new_date = date('F d, Y', mktime(0, 0, 0, $month, $day, $year));
	  $a = substr($data3, 0, 2);        
	  $b = substr($data3, 2, 4);        
	  $am_or_pm = (int)$a >= 12 ? "pm" : "am";        
	  if ($a > 12) { $a = $a - 12; }           
	  $newtime = $a.':'.$b.$am_or_pm;
	  
     echo "<b>$data</b> is scheduled on $new_date at $newtime.";
	 echo "<br />";
	 

   } 
} 
else 
{ 
   echo "<p>No outings currently scheduled.</p>\n"; 
} 

?>

Code (markup):

I know some of my variable names are a mess, but the query is pretty straight forward.

Anyway, all I am looking to do is be able to group it so instead of what I posted above, I get a result like this:

Any ideas on where I should start to code this?

Thanks!

 

I didn't get you. You're getting only "Outing: test outing is scheduled on January 01, 2011 from 06:00am to 06:56am." and you don't know why you aren't getting another results?

If so, so it's easy to answer.

it's searching only data with name '*outing*' (* = wildchar) because you specified it there. If it's not right answer, I apologies for that

 

It should be doable with single SQL query, try this:

SELECT `name`,
            Min(DATE_ADD(`date` , INTERVAL `time` HOUR_SECOND)) as DateTimeFrom,
            Max(DATE_ADD(`date` , INTERVAL `time` HOUR_SECOND)) as DateTimeTo 
FROM `data`
GROUP BY `name`

PHP:

It will group all records by name and return Min and Max DateTime for grouped record.

If you want to group it by hour or day like in your example, then you will need to tweak the query a bit, I'll leave that to you as exercise

 

This seems to be exactly what I was looking for actually. I'll play around with that tomorrow and see what I can get working.

Thanks!