Multidimensional Array output php mysql

Question

Was wondering if somebody could help me with how to construct a multidimensional array output, have been stuck on this for quite some time now. Im using PHP and MySQL database I have 3 tables 'Trophies' 'Winners' and 'LinkTable' (Full structure is below) I need to display all the trophy data (name description image) and the a table below showing all the previous winners of the trophy;

This is the desired output…

Trophy1Name                    [Trophy1Image]
Trophy1Description
Year FirstName SecondName

I need this to loop for all the other trophies in the DB (9 total) hence why I need the array, problem im having is, I can show all the trophies with their image and description in a loop but I cant work out how to add all the previous winners to the table.

The closest iv come is to loop each winner but this also repeats the trophy details over and over again not exactly ideal when I have over 300 different winners.

This is my mysql table structure and join:

Trophies     Winners     LinkTable
--------    ---------    ---------
TrophyID     WinnerID    LT_WinnerID
TrophyName   FirstName   LT_TrophyID
Description  SecondName
Image         Year

Join:

$trophylist = mysql_query("
SELECT TrophyID, TrophyName, Description, Image, WinnerID, FirstName, SecondName, Year 
FROM LinkTable
INNER JOIN Winners ON (LinkTable.LT_WinnerID = Winners.WinnerID) 
INNER JOIN Trophies ON (LinkTable.LT_TrophyID = Trophies.TrophyID)");

Answer 1

That doesn't sound like a query problem. Sounds more like a display problem. The DB is going to naturally return all the winners of a trophy, which requires the result set to repeat the name of that trophy in every record. It's up to you to write the PHP code to properly interpret that.

e.g. in pseudo-ish code:

$prev_trophy = null;
while($row = fetch_from_db($query_result)) {
   if ($row['TrophyID'] != $prev_trophy) {
      ... got a new trophy, so output trophy header stuff here
      $prev_trophy = $row['TrophyID'];
   }
   ... output winner information
}

As long as you have the appropriate order by clause in your query, the above style of code will output a trophy's details ONCE as a header. For subsequent records of the same trophy, only the winner information will be output