CODEWITHSUNDEEP
phpmysqlforeach
user3527102
user3527102

Reputation: 5

foreach loop with mysql link table

I have 3 MySQL tables, Trophies, Winners and LinkTable I need to output the results in a way that all the previous winners of each trophy will be listed under each trophy because currently each result is just looping giving me a ton on data.

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

SQL Join is as follows 

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

If i output the results through a while loop, as expected returns a new line for each winner with the trophy alongside it.

I think it is a foreach loop that I need to use so that an array can be created for the trophy, but Im a little stuck for ideas now as everything I'm finding doesnt seem to make much sense so anybody pointing me in the right direction would be greatly appreciated.

Upvotes: 0

Views: 100

Answers (1)

Bill
Bill

Reputation: 13

You could re-sort your data into a 2-dimensional array, indexed by trophy and year. 

$n = mysql_num_rows($trophylist);
for ($i=0; $i<$n; $i++)
{
  $row = mysql_fetch_array($trophylist);
  $trophyId = $row['TrophyID']; 
  $year = $row['Year'];
  $newlist["$trophyId"]["$year"] = $row; 
}

You could then easily use the $newlist array to outputs all winners of a given trophy or all winners in a given year.

Upvotes: 1

Related Questions