FOREACH Loop issues

[JonB]

I've spent way too long on this and must be overlooking something... I'm setting up an honor board for our club. I have a query that lists the year, regatta, event, result, and crew.

I'm trying to have it render in groups:

Year
Regatta
Event Result Crew
Event Result Crew
Event Result Crew
Year
Regatta
Event Result Crew
Event Result Crew
Event Result Crew
Regatta
Event Result Crew
Event Result Crew
Event Result Crew

So here's my code:

<?php

// Include the configuration file for error management and such.
require_once ('../../private/config.inc.php');

require_once ('../../private/mysql_connect.php'); // Connect to the database.
include ('./includes/Header.php');
$query = "SELECT YEAR(HonorRollEvents.ActivityStartDate) as EventYear, HonorRollEvents.ActivityName, HonorRoll.HonorRollID, HonorRoll.HonorRollActivityID, HonorRoll.EventName, HonorRoll.Result, HonorRoll.Crew
FROM LSRC.HonorRoll INNER JOIN LSRC.HonorRollEvents ON HonorRoll.HonorRollActivityID = HonorRollEvents.HonorRollActivityID
ORDER BY ActivityStartDate DESC";

$result = mysql_query($query) or die('Error, query failed');

$set = array();

while ($record = mysql_fetch_object($result)) {
$set[$record->EventYear][$record->ActivityName][] = $record;
}
print "<table width = '100%'>\n";

foreach ($set as $EventYear => $ActivityNames) {
print "<tr bgcolor = 'yellow'> <td colspan = '3'>{$EventYear}</td></tr>\n";
print "<tr bgcolor = 'yellow'><td>Event</td> <td>Result</td><td>Crew</td></tr>\n";

foreach ($ActivityNames as $ActivityName => $HonorRollIDs) {
  print "<tr bgcolor = 'yellow'><td colspan = '3'>{$ActivityName}</td></r>\n";

	foreach ($HonorRollIDs as $HonorRollID =>$record) {
	  echo "<tr bgcolor = 'yellow'><td>";
          echo $record['EventName'];
          echo "</td><td>";
          echo $record['Result'];
          echo "</td><td>";
          echo $record['Crew'];
          echo "</td></tr>\n";

}
print "</table>\n";
}
}
?>

Can anyone point out what I'm screwing up? I get the first year and regatta listed but no results etc.

Many thanks.

[paulnaj]

Hi,

Hard to tell ... don't know how your tables are built. But try this to see what results you get ...

$query = "
SELECT YEAR(HonorRollEvents.ActivityStartDate) as EventYear
, 	HonorRollEvents.ActivityName
, 	HonorRoll.HonorRollID
, 	HonorRoll.HonorRollActivityID
, 	HonorRoll.EventName
, 	HonorRoll.Result
, 	HonorRoll.Crew
FROM LSRC.HonorRoll 
LEFT JOIN LSRC.HonorRollEvents ON (HonorRoll.HonorRollActivityID = HonorRollEvents.HonorRollActivityID)
ORDER BY HonorRollEvents.ActivityStartDate DESC
";

$result = mysql_query($query) or die('Error, query failed');

echo '<pre>'; 
while ($record = mysql_fetch_object($result)) {
	echo '<br>'; var_dump($record);
}
echo '</pre>';

P.