Hi,
I have a problem with getting information out of a table as two different records are needed from one table.

I have a page that holds a report for what happend in a football match.

I need to show who scored for which team.
I have it working for Team 1 but the results are the same for Team 2

Please take a look:

PHP Code:
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);

mysql_select_db($database_db$db);
$query_cardsMOM "SELECT * FROM match_player";
$cardsMOM mysql_query($query_cardsMOM$db) or die(mysql_error());
$row_cardsMOM mysql_fetch_assoc($cardsMOM);
$totalRows_cardsMOM mysql_num_rows($cardsMOM);

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);

And the page HTML:

PHP Code:
        <h1>Match Report</h1>
        <br />
        <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>

        <p>
<?php /*?>        <h3>Report</h3>
        <?php echo $row_history['report']; ?><?php */
?>

        <br />
        </p>