[thelist] Problem with SQL Query - using PHP and MySQL

[Pringle, Ron]

Final followup for anyone interested.

I ended up using Phil's suggestion for the query, i.e. using group
concat, even though I actually needed two values from the eventDates
table. Since one was the unique ID assigned to each record, there was a
1 to 1 relationship for each date, so it didn't pose a problem. Below is
the final SQL statement:

SELECT title, summary, eventID, Group_Concat(dateID ORDER BY eventDate
Separator ', ')AS DateID, Min(eventDate) AS FirstDate,
Group_Concat(DATE_FORMAT(eventDate, '%c/%e') ORDER BY eventDate
Separator ', ') AS EventDateString FROM tbl_event INNER JOIN
tbl_eventDates USING (eventID) WHERE eventDate >= Current_Date GROUP BY
title ORDER BY FirstDate

In PHP, I then exploded the two comma delimited string values into
arrays and looped through them for each record. Below is the essential
PHP code:

//loop through all records
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	echo "<h5 class='sansheader'>{$row['title']}</h5>\n";
	echo $row['summary'] . "\n";
	echo "<h3>Event Date(s):</h3>\n";
	//put comma delimited strings into arrays
	$arrayID = explode(", ", $row["DateID"]);
	$arrayDate = explode(", ", $row["EventDateString"]);
	//set length of array and loop through
	$arrayLength = count($arrayID);
	for ($i = 0; $i < $arrayLength; $i++) {
		echo "<p>" . $arrayDate[$i] . " - <a
href='/detail.php?dateID=" .
		$arrayID[$i] . "'>More Info</a></p>\n";
		};
	echo "<div class='exhibitdivider'></div>\n";
	}

The end result is a nice list of events, listed by first date of
occurance and grouped by title where multiple dates for each event are
listed under the title.

Thanks to those who helped, especially Phil. Much appreciated.

Regards,
Ron