football/soccer match report problem php/mysql

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:

[code=php]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);
}
[/code]

And the page HTML:

[code=php] <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>[/code]

Also in #PHP _↴