Group mysql result without nested query?

[TQ_designs]

I've searched through older threads but haven't yet found a solution for the following issue: Is it possible to group and arrange mysql results without resorting to nested queries? Here's an approximate sample of how my results currently display:

Time 1 Station 1
Time 1 Station 2
Time 1 Station 3
Time 2 Station 1
Time 2 Station 2
etc.

I want them to display like this:

Time 1
- Station 1
- Station 2
- Station 3
Time 2
- Station 1
- Station 2
etc.

Here's an excerpt from my present code:

while($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
	echo "<li>\n";
	echo "{$row['time']} at Station {$row['stationID']} ";
	echo "<input type='radio' name='chooseTime' value='{$row['stationID']}~{$row['time']}~{$row['slotID']}'> Select</input>\n";
	echo "</li>\n";
}

I'd prefer not to have to nest a loop to fetch the stations within the loop that fetches times, so is there a better/more efficient way?

[ixalmida]

You didn't post your query, but wouldn't it be easiest just to add "GROUP BY time" to the SELECT query?

By the way, you may want to rethink using a MySQL reserved word as a field name (i.e. - "time", "date", etc.).

[TQ_designs]

I tried "group by" earlier but it didn't work - I don't want to eliminate results with the same time value, just make them display differently (i.e. once per unique time).

Here's the full query:

$query = "SELECT * from slotTest"
	. " JOIN resTest ON slotTest.slotID=resTest.slotID"
	. " JOIN exp ON slotTest.expID=exp.expID"
	. " JOIN times ON slotTest.timeID=times.timeID";
$query .= " WHERE slotTest.courseID={$courseID}" 
	. " AND slotTest.weekID={$week}" 
	. " AND slotTest.dayID={$dayID}" 
	. " AND slotTest.expID={$exp}"
	. " AND slotTest.spots<3";

I need the time/station info to be included in each <input>, but I'd like the end user to see the results displayed as per my post above.

[ixalmida]

I see. Well...you'll just have to insert an if() statement and a $prev_time value.

// Initialize previous time...
$prev_time = "";
while($row=mysql_fetch_array($result,MYSQL_ASSOC))
{
  // If this is a different time than last, print a new header:
  if($prev_time != $row['time'])
    echo "<strong>Time {$row['time']}</strong>\n";
  // Print the list...
  echo "<li>\n"; 
  echo "{$row['time']} at Station {$row['stationID']} "; 
  echo "<input type='radio' name='chooseTime' value='{$row['stationID']}~{$row['time']}~{$row['slotID']}'> Select</input>\n"; 
  echo "</li>\n"; 
  // Set previous time to current time...
  $prev_time = $row['time'];
}

You may need to work <ul></ul> into that somewhere.

[TQ_designs]

Thanks! That worked perfectly 🙂 For anyone interested, here's the final code I wound up using:

$prevTime = "";
echo "<div class='timeSlots'>\n";
while ($row=mysql_fetch_array($result,MYSQL_ASSOC)) {
	// If this is a different time than last, print a new header:
	if ($prevTime != $row['time']) {
		if ($prevTime != "") { // to skip the closing </ul> tag for the first time returned
			echo "</ul>\n";
		}
		echo "<ul>\n";
		echo "<li><strong>{$row['time']}</strong></li>\n";
	}
	// Print the list...
	echo "<li>\n";
	echo "<input type='radio' name='chooseTime' id='radio{$row['slotID']}'" . 
		" value='{$row['stationID']}~{$row['expName']}~{$day}~{$row['time']}~{$row['slotID']}~{$row['courseID']}' />\n";
	echo "<label for='radio{$row['slotID']}'>Station {$row['stationID']}</label>";
	echo "</li>\n";
	// Set previous time to current time...
	$prevTime = $row['time'];
}
echo "</div>\n";