Hi all
I've been putting together a site for a film awards ceremony at the moment.
I've had a lot of help over on the mySQL board with a db structure to allow me to select a list of awards and their winners (which could be either a person, a person for a film, more than one person, more than one person for a film, orjust a film).
I'm using this code to get the query results:
<?php
$result = @mysql_query("
SELECT award_ceremony.id, award, title, firstname, lastname FROM winner
LEFT JOIN film ON film_id=film.id
LEFT JOIN people ON people_id=people.id
LEFT JOIN award_ceremony ON award_ceremony_id=award_ceremony.id
LEFT JOIN award ON award_id=award.id
WHERE ceremony_id=$ceremonyid" );
if (!$result) {
echo("<p>ERROR:" . mysql_error() ."</p>" );
exit ();
}
while ($awardlist = mysql_fetch_array($result)) {
$id = $awardlist["award_ceremony.id"];
$award = htmlspecialchars($awardlist["award"]);
$title = htmlspecialchars($awardlist["title"]);
$firstname = htmlspecialchars($awardlist["firstname"]);
$lastname = htmlspecialchars($awardlist["lastname"]);
echo ("<tr><td>$award</td><td>$firstname $lastname " );
if ($lastname AND $title) {
echo("(" );
}
echo ("$title" );
if ($lastname AND $title) {
echo(" )" );
}
echo("</td></tr>" );
}
?>
But what this gives me is a result that might look a bit like this
Award Winner
Best Film Film Name
Best Actor John Doe (Film Name)
Best Screenplay Jack Smith (Film Name)
Best Screenplay Jane Smith (Film Name)
The first 2 are fine, but because there are two people who have won best screenplay (and so make up 2 records in the winners table) they get outputted on 2 lines. What I want is the output to look like this:
Award Winner
Best Film Film Name
Best Actor John Doe (Film Name)
Best Screenplay Jack Smith, Jane Smith (Film Name)
this is the code I've come up with so far
<?php
$result = @mysql_query("SELECT award_ceremony.id AS award_ceremony_id, award, film_id, title
FROM winner
LEFT JOIN film ON film_id=film.id
LEFT JOIN award_ceremony ON award_ceremony_id=award_ceremony.id
LEFT JOIN award ON award_id=award.id
WHERE ceremony_id=$ceremony" );
if (!$result) {
echo("<p>ERROR:" . mysql_error() ."</p>" );
exit ();
}
while ($awardlist = mysql_fetch_array($result)) {
$ceremonyid = $awardlist["award_ceremony_id"];
$filmid = $awardlist["film_id"];
$award = htmlspecialchars($awardlist["award"]);
$title = htmlspecialchars($awardlist["title"]);
$people = @mysql_query("SELECT firstname, lastname FROM winner
LEFT JOIN people ON people_id=people.id
WHERE award_ceremony_id=$ceremonyid" );
if (!$people) {
echo("<p>ERROR:" . mysql_error() ."</p>" );
exit ();
}
while ($peoplelist = mysql_fetch_array($people)) {
$firstname = htmlspecialchars($peoplelist["firstname"]);
$lastname = htmlspecialchars($peoplelist["lastname"]);
$peoplenames = ("$firstname $lastname" );
}
echo ("<tr><td>$ceremonyid</td><td>$award</td><td>$peoplenames " );
if ($peoplenames AND $title) {
echo("(" );
}
echo ("<a href="film.php?film=$filmid">$title</a>" );
if ($peoplenames AND $title) {
echo(" )" );
}
echo("</td></tr>" );
}
?>
But of course, at the moment, the $peoplenames variable just contains the first name in the array. How do I get all the names out of the array into a single string which I can then write using an echo statement?
