foreach loop with mysql link table

Question

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.

What's the while loop you have so far? How are you outputting the results? You can create an array using a while loop, too. — gen_Eric
– gen_Eric, Commented Nov 10, 2014 at 22:48
<?php while( $row = mysql_fetch_array($trophylist) ){?> This is the loop im using currently — user3527102
– user3527102, Commented Nov 10, 2014 at 22:50
In that loop, just take the values you need from $row and add them into the array that you want. — gen_Eric
– gen_Eric, Commented Nov 10, 2014 at 22:53
im sorry but im still stuck could you possibly give me an example ? — user3527102
– user3527102, Commented Nov 10, 2014 at 23:13

Bill · Accepted Answer · 2014-11-11 00:30:45Z

1

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.

answered Nov 11, 2014 at 0:30

Bill

135 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3527102 Over a year ago

Thanks for the response, I can see the array works when I print the array I get all the information, sorry to be a pain but how do I go about displaying this data properly iv never done anything like this before.

Bill Over a year ago

First change the last but one line in the code above to $newlist["$year"] ["$trophyId"].

Bill Over a year ago

Then you could try the following:

foreach($newlist as $trophyArray) {   foreach($trophyArray as $yearArray)   {     echo "$yearArray['TrophyName'] : $yearArray['Year'] : $yearArray['WinnerName'] <br/>";   }   echo "<br/>"; }

Bill Over a year ago

To get the years in order you should add an ORDER BY clause in your original SQL query.

Collectives™ on Stack Overflow

foreach loop with mysql link table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related