two records from the same table in for loop

[catalepticstate]

I have a table that holds teams, a table that holds scores and a table that holds match data.

I am finding it dificult to get the player data for the second team.

Currently It is showing player information for one team.

It does echo out the second team name, but not their player info.

Can someone have a look at my code and make it work.
I took off the code I had for the second team becuase it was ging me Error 1065

[B]Main Query:[/B]
mysql_select_db($database_db, $db);
$query_match_fixtures = "select m.match_id, date_format(m.date, '%d/%m/%Y') as mDate, m.time, t1.division, m.report, t1.team_name as team1_name, s1.score as score1, t2.team_name as team2_name, s2.score as score2
from matches m left join (matchscores s1 left join team t1 on t1.team_id = s1.team) on (s1.match_id = m.match_id) left join (matchscores s2 left join team t2 on t2.team_id = s2.team) on (s2.match_id = m.match_id)
where s1.team <> s2.team AND m.match_id = $matchID
group by match_id
order by m.match_id";
$match_fixtures = mysql_query($query_match_fixtures, $db) or die(mysql_error());
//$row_match_fixtures = mysql_fetch_assoc($match_fixtures);
$totalRows_match_fixtures = mysql_num_rows($match_fixtures);
[B]
Player extraction:[/B]
mysql_select_db($database_db, $db);

$row_match_player = array();

for ($i = 0; $i < $totalRows_history; $i++)
{
    $row_history[$i] = mysql_fetch_assoc($history);
}

for ($i = 0; $i < $totalRows_match_fixtures; $i++)
{
    $row_match_fixtures[$i] = mysql_fetch_assoc($match_fixtures);

    $match_player_query = "SELECT *
                           FROM match_player
                            LEFT JOIN player on player.player_id = match_player.player_id
                            LEFT JOIN team ON player.team_id = team.team_id
                           WHERE match_id = ".$matchID."
                            AND team.team_name = '".$row_match_fixtures[$i]['team1_name']."'";


    $result_match_player = mysql_query($match_player_query, $db) or die("large error ".mysql_errno());

    $totalRows_match_player = mysql_num_rows($result_match_player);


    for ($r; $r < $totalRows_match_player; $r++)
    {
        $temp_row_match_player[$r] = mysql_fetch_assoc($result_match_player);
    }
    array_push($row_match_player, $temp_row_match_player);
}

[B]The display table:[/B]
        <div class="tableHeading">
        <h2><?php echo $row_history['mDate']; ?></h2>
        </div>
        <table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td height="25" bgcolor="#000000"><div align="left" style="color:#FFFFFF"><strong>Type</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="left" style="color:#FFFFFF"><strong>Home</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Score</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Away</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Kick-Off</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Venue</strong></div></td>
    <td height="25" bgcolor="#000000"><div align="center" style="color:#FFFFFF"><strong>Referee</strong></div></td>
  </tr>
  <tr>
    <?php foreach ($row_match_fixtures as $show_match_fixtures)
    { ?>
        <td height="25" bgcolor="#dfdfdf">
        <div align="left"><?php echo $show_match_fixtures['division']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">
        <div align="left"><?php echo $show_match_fixtures['team1_name']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">
        <div align="center"><?php echo $show_match_fixtures['score1']; ?> - <?php echo $show_match_fixtures['score2']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">
        <div align="center"><?php echo $show_match_fixtures['team2_name']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">

        <!-- You do not want a nested loop inside the header so these results will have to go inside the table-->

        <div align="center"><?php echo $row_history['time']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">
        <div align="center"><?php echo $row_history['venue_name']; ?></div>
        </td>
        <td height="25" bgcolor="#dfdfdf">
        <div align="center"><?php echo $row_history['fname']; ?> <?php echo $row_history['sname']; ?></div>
        </td>
    </tr>
      <tr>
        
        
        <?php foreach ($row_match_player as $player_array)
        {
            foreach ($player_array as $player_display)
            {
        ?>
        <td valign="top">Goalscorers</td>    
        <td>
    
  
        <?php echo $player_display['fname']." ".$player_display['sname']." (".$player_display['Goals']; ?>)<br />
        

        </td>
        <td>&nbsp;</td>

        <td>
    
  
        <?php echo $player_display['fname']." ".$player_display['sname']." (".$player_display['Goals']; ?>)<br />
        

        </td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td valign="top">Yellow Cards</td>
        <td><?php echo $player_display['YC']; ?></td>
        <td>&nbsp;</td>
        <td><?php echo $player_display['YC']; ?></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td valign="top">Red Cards</td>
        <td><?php echo $player_display['RC']; ?></td>
        <td>&nbsp;</td>
        <td><?php echo $player_display['RC']; ?></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td valign="top">Man of the Match</td>
        <td><?php echo $player_display['fname']; ?> <?php echo $player_display['sname']; ?></td>
        <td>&nbsp;</td>
        <td><?php echo $player_display['fname']; ?> <?php echo $player_display['sname']; ?></td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
        <td>&nbsp;</td>
      </tr>
            <?php
                }// close and unset $player_display
                unset($player_display);
            } //close and unset $player_array
        unset($player_array);
     } //close and unset $show_match_fixtures
    unset($show_match_fixtures);?>
</table>

http://forums.adobe.com/servlet/Jive...Untitled-1.jpg

As you can see from the picture above the table is displayed with the name of the "goal scorer" for that team under (home) or Home team

When I try to create the same thing for away team I get a error, either a syntax error or 1065.

I tried having a foreach do two queries but that did not work.

Any help would be appriecated

[catalepticstate]

Someone suggested the for loop was wrong

[MrBaseball34]

Why not just join the match_player table in the first query to return all the players for the match instead of doing it in a loop?

[catalepticstate]

I will give that a try

[catalepticstate]

I've messed it up nothing works :'(