MYSQL outputing help

[Aravinthan]

Hi,

I am making a personal site about me and I wanted to show my teams last 5 games scores and the scorers. So I made a DB:

Gameid

gf

ga

winner

lname

goals

assists

points

And now I am outputting it:

<?php

$link = mysql_connect ("localhost", "user", "pass")

or die("mysql_error()");

mysql_select_db ("stmaxel_stats", $link);

$result = mysql_query("SELECT * FROM `games` ORDER BY gameid DESC LIMIT 1 ",$link);

while($row = mysql_fetch_array($result))

  {

  echo "Game Number: " .$row['gameid']. "</br>";

  echo "A " .$row['gf']. "-" .$row['ga']. " " .$row['winner']. "<br/>";

  echo "Scorers:<br/>";

                                                echo "<center><table border='1' width='100%' bordercolor='#413326'>

  <tr>

  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Lastname</font>

  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Goals</font>

  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Assists</font>

  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Points</font>";

                                                echo "</tr>

                                                      <tr>";

                                                echo "<td>" . $row['lname'] . "</td>";

echo "<td width='7%'>" . $row['lnameg'] . "</td>";

echo "<td width='7%'>" . $row['lnamea'] . "</td>";

                                                echo "<td width='7%'>" . $row['lnamep'] . "</td>";

                                                echo "</tr></table></center>";

                                                }

?>

The thing is that it outputs the same game 2 times. I just saw my mistake, I enter the different players who had points in this game. But differently. So there is more than 1 entry for the same game. And when I try to output it, it shows in 2 different tables. Is there any way that I can show it in only 1 table?

I am not sure if I am clear, if you dont understand let me know and I'll try to clear myself.

 

THanks for your help and time,

Ara

[Chicken Little]

You should normalize your table, put the games into one table and the players into another. You then do a JOIN for the data you need.

 

Also, if you want to see the last five your sql should DESC LIMIT 5.

[Aravinthan]

Ok, so I changed my tables:

games:

gameid, ga, gf and winner

players:

gameid, goals, assists and points.

And this is my coding:

result = mysql_query("SELECT * FROM games, players WHERE games.gameid = players.gameid  ORDER BY gameid DESC LIMIT 5 ",$link);
										while($row = mysql_fetch_array($result))
										  {
										  	echo "Game Number: " .$row['gameid']. "</br>";
										  	echo "A " .$row['gf']. "-" .$row['ga']. " " .$row['winner']. "<br/>";
										  	echo "Scorers:<br/>";
                                                echo "<center><table border='1' width='100%' bordercolor='#413326'>
												  <tr>
												  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Lastname</font>
												  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Goals</font>
												  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Assists</font>
												  <th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Points</font>";
                                                echo "</tr>
                                                      <tr>";
                                                echo "<td>" . $row['lname'] . "</td>";
											echo "<td width='7%'>" . $row['lnameg'] . "</td>";
											echo "<td width='7%'>" . $row['lnamea'] . "</td>";
                                                echo "<td width='7%'>" . $row['lnamep'] . "</td>";
                                                echo "</tr></table></center>";
                                                }

 

But there is no data that is getting displayed....

 

Thanks for your help,

Ara

[fenway]

Check mysql_error(), and the number of rows returned.

[Chicken Little]

I've gotten this to work with a similar database. You may be able to modify where needed.

$result = mysql_query("SELECT * FROM games
ORDER BY games.game_id DESC LIMIT 5",$link);
while($row = mysql_fetch_assoc($result))
{
$result2 = mysql_query("SELECT * FROM players
WHERE game_id='{$row['game_id']}'",$link);
$nrows=mysql_num_rows($result2);
echo "Game Number: " .$row['game_id']. "</br>";
echo "A " .$row['gf']. "-" .$row['ga']. "-"  .$row['winner']. "<br/>";
echo "Scorers:<br/>";
echo "<center><table border='1' width='100%' bordercolor='#413326'>
<tr>
<th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Lastname</font>
<th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Goals</font>
<th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Assists</font>
<th align='center' BGCOLOR='#413326'><FONT COLOR='#FFFFFF' size='3'>Points</font></tr>";
if($nrows >0)
{
while($row2 = mysql_fetch_assoc($result2))
{
echo "<tr>";
echo "<td width='7%'>" .$row2['lname']. "</td>";
echo "<td width='7%'>" .$row2['lnameg']. "</td>";
echo "<td width='7%'>" .$row2['lnamea']. "</td>";
echo "<td width='7%'>" .$row2['lnamep']. "</td>";
echo "</tr>";
}
echo "</table></center>";
}
}