While grouping Player Award, do not group season/year of Award

[predator]

Okay, I figure my problem is best solved in PHP and not in the actual SQL query, but please correct me if I am wrong.

BACKGROUND
I have a database with players. Each player may receive various awards. Each award is given to the player a certain Season. A player can receive an award numerous times, but different seasons.

PROBLEM
I want to list all the Awards the player has received, also showing the year he won the award. However, I wish to have the Awards grouped (not listing the same awards numerous time), but not have the seasons of the award grouped.

CURRENT OUTPUT

World Championship Most Points 1997-1998
World Championship Silver Medal 1992-1993
World Championship Silver Medal 2002-2003
World Championship Silver Medal 2003-2004

DESIRED OUTPUT

World Championship Most Points 1997-1998
World Championship Silver Medal 1992-1993, 2002-2003, 2003-2004

DATABASE STRUCTURE

ID (auto_increment)
PlayerID (identifies the player)
Season
Awards (the actual award)
Extra (additional information about the award)

CURRENT CODING

$query = "SELECT Season, Awards, Extra FROM awards WHERE PlayerID='710' ORDER BY Awards ASC";
$result = mysql_query($query,$db_link);
$rows = mysql_num_rows($result);

for($index = 0; $index < $rows; $index++) {
$Season = mysql_result($result, $index, "Season");
$Awards = mysql_result($result, $index, "Awards");
$Extra = mysql_result($result, $index, "Extra");

echo "&nbsp;$Awards"; if ($Extra != ""){echo " ($Extra)"; } echo " $Season";
}

Any ideas/thoughts? If I change the query to GROUP BY Awards, it would result in Season being grouped with it too...

[NogDog]

You could build a 2-D array from your query results, where the first key would be the award, and then the 2nd key would be an auto-incremented index

$data = array();
$query = "SELECT Season, Awards, Extra FROM awards WHERE PlayerID='710' ORDER BY Awards ASC";
$result = mysql_query($query,$db_link);
while($row = mysql_fetch_assoc($result))
{
   $text = $row['Season'];
   if($row['Extra'] != '')
   {
      $text = '('.$row['Extra'].') '.$text;
   }
   $data['Award'][] = $text;
}
foreach($data as $key => $val)
{
   echo "<li>$key " . implode(', ', $val) . "</li>\n";
}

[predator]

Thanks for the quick reply NogDog! That type of coding is new to me, but what better way to learn 🙂

However, I am a bit confused. Where/how should I let it print out the actual award? I.e.
$row['Awards']; ?

I tried to add it in the above code, but was not successful.

Right now, the above is shown as

• Award 1997-1998, 1992-1993, 2002-2003, 2003-2004

..instead of:

• World Championship Most Points 1997-1998

• World Championship Silver Medal 1992-1993, 2002-2003, 2003-2004

[NogDog]

This line:

$data['Award'][] = $text; 

Should have been:

$data[$row['Awards']][] = $text;

[predator]

Thank you, I appreciate it 🙂

[NogDog]

You're welcome, and don't forget to mark this thread "Resolved" (if it is) via the "Thread Tools" link at the top of the thread.