[RESOLVED] query prob

[msnhockey]

hey all,

In my code below. It is supposed to list all players associated with a game that had played in that game.. well I get no errors, and the code only lists the first player that played in that game and leaves all the other rows blank except for the ( ) where the player postion goes..

here is the code if someone could take a look

$stats_sql = "SELECT * FROM players, player_stats " 
     . "WHERE players.teamid = '".$gm_team2."' AND players.position != 'G' " 
     . "AND players.registered = '1' AND players.id = player_stats.playerid " 
     . "AND player_stats.game_id = '".$id."' AND player_stats.played = '1' " 
     . "ORDER BY lname"; 

$result_stats = mysql_query("$stats_sql");
	if(!$result_stats) { echo mysql_errno().": ".mysql_error()."<br>"; }



$statcount = mysql_numrows($result_stats);

$sc = 0;
$i = 0;
        $stats .= "

                <p class=\"header\" align=\"center\">Player Stats</p>

                <table border=\"0\" cellpadding=\"2\" cellspacing=\"1\" align=\"center\" width=\"99%\">

                <tr bgcolor=\"#dfdfdf\" align=\"center\">

                <td class=\"table_header\">Name</td>

                <td class=\"table_header\">G</td>

                <td class=\"table_header\">A</td>";



        if($user_xtra1 == 1){

        $stats .= "<td class=\"table_header\">$user_x1titles</td>";

        }

        if($user_xtra2 == 1){

        $stats .= "<td class=\"table_header\">$user_x2titles</td>";

        }

        if($user_xtra3 == 1){

        $stats .= "<td class=\"table_header\">$user_x3titles</td>";

        }

        if($user_xtra4 == 1){

        $stats .= "<td class=\"table_header\">$user_x4titles</td>";

        }



        $stats .= "

    	        <td class=\"table_header\">PIM</td>


                </tr>";

while ($row = mysql_fetch_assoc($result_stats)) 
    {

while($i < $statcount) {

$tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";
$pid = mysql_result($result_stats,0,"players.id");
$goals = mysql_result($result_stats,0,"player_stats.goals");
$assists = mysql_result($result_stats,0,"player_stats.assists");
$minutes = mysql_result($result_stats,0,"player_stats.minutes");
$xtra1 = mysql_result($result_stats,0,"player_stats.xtra1");
$xtra2 = mysql_result($result_stats,0,"player_stats.xtra2");
$xtra3 = mysql_result($result_stats,0,"player_stats.xtra3");
$xtra4 = mysql_result($result_stats,0,"player_stats.xtra4");
$shotper = ROUND($goals / $xtra4 * 100, 2); 
$xtra5 = mysql_result($result_stats,0,"player_stats.xtra5");
$position = mysql_result($result_stats,0,"players.position");
$fname = mysql_result($result_stats,0,"players.fname");
$lname = mysql_result($result_stats,0,"players.lname");





$stats .= "

<tr bgcolor=\"".$tbgcolor."\" align=\"center\">
<td class=\"table_text\">
$fname $lname ($position) </td>


<td class=\"table_text\">$goals</td>
<td class=\"table_text\">$assists</td>";

if($user_xtra1 == 1){
$stats .= "<td class=\"table_text\">$xtra1</td>";
}

if($user_xtra2 == 1){
$stats .= "<td class=\"table_text\">$xtra2</td>";
}

if($user_xtra3 == 1){
$stats .= "<td class=\"table_text\">$xtra3</td>";
}

if($user_xtra4 == 1){
$stats .= "<td class=\"table_text\">$xtra4</td>";
}

$stats .= "
<td class=\"table_text\">$minutes</td></tr>";







			   $i++;

		unset($result_stats);
}  }

[niroshan]

First of all I see two problems with your code,

problem 1:
since you are looping through all your records using,

while ($row = mysql_fetch_assoc($result_stats)) 
    {

why do you need the,

while($i < $statcount) 
{

for ?

Problem 2:

This is what cause to empty all the data except the first record.

unset($result_stats);

this should happen after existing the while loops. otherwise as soon as you write the first record you are unseting the variable so for next round that $result_stats array will be empty so it prints the empty values.

try this,

<?php
$i=0;
while ($row = mysql_fetch_assoc($result_stats)) 
{
	$tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";
	$pid = mysql_result($result_stats,0,"players.id");
	$goals = mysql_result($result_stats,0,"player_stats.goals");
	$assists = mysql_result($result_stats,0,"player_stats.assists");
	$minutes = mysql_result($result_stats,0,"player_stats.minutes");
	$xtra1 = mysql_result($result_stats,0,"player_stats.xtra1");
	$xtra2 = mysql_result($result_stats,0,"player_stats.xtra2");
	$xtra3 = mysql_result($result_stats,0,"player_stats.xtra3");
	$xtra4 = mysql_result($result_stats,0,"player_stats.xtra4");
	$shotper = ROUND($goals / $xtra4 * 100, 2); 
	$xtra5 = mysql_result($result_stats,0,"player_stats.xtra5");
	$position = mysql_result($result_stats,0,"players.position");
	$fname = mysql_result($result_stats,0,"players.fname");
	$lname = mysql_result($result_stats,0,"players.lname");

$stats .= "
<tr bgcolor=\"".$tbgcolor."\" align=\"center\">
<td class=\"table_text\">
$fname $lname ($position) </td>
<td class=\"table_text\">$goals</td>
<td class=\"table_text\">$assists</td>";

if($user_xtra1 == 1)
{
	$stats .= "<td class=\"table_text\">$xtra1</td>";
}

if($user_xtra2 == 1)
{
	$stats .= "<td class=\"table_text\">$xtra2</td>";
}

if($user_xtra3 == 1)
{
	$stats .= "<td class=\"table_text\">$xtra3</td>";
}

if($user_xtra4 == 1)
{
	$stats .= "<td class=\"table_text\">$xtra4</td>";
}

$stats .= "
<td class=\"table_text\">$minutes</td></tr>";
$i++;	
}
unset($result_stats);
?>

Thanks,
Regards,
niroshan

[msnhockey]

tryed your code and get the same name about 18 times..

[niroshan]

sorry about my earlier post, I have missed that your using mysql_result () instead of mysql_fetch_array.

try this,

$statcount = mysql_num_rows($result_stats);
$i=0;
while($i<$statcount )
{
    $tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";
    $pid = mysql_result($result_stats,$i,"players.id");
    $goals = mysql_result($result_stats,$i,"player_stats.goals");
    $assists = mysql_result($result_stats,$i,"player_stats.assists");
    $minutes = mysql_result($result_stats,$i,"player_stats.minutes");
    $xtra1 = mysql_result($result_stats,$i,"player_stats.xtra1");
    $xtra2 = mysql_result($result_stats,$i,"player_stats.xtra2");
    $xtra3 = mysql_result($result_stats,$i,"player_stats.xtra3");
    $xtra4 = mysql_result($result_stats,$i,"player_stats.xtra4");
    $shotper = ROUND($goals / $xtra4 * 100, 2);
    $xtra5 = mysql_result($result_stats,$i,"player_stats.xtra5");
    $position = mysql_result($result_stats,$i,"players.position");
    $fname = mysql_result($result_stats,$i,"players.fname");
    $lname = mysql_result($result_stats,$i,"players.lname");

$stats .= "
<tr bgcolor=\"".$tbgcolor."\" align=\"center\">
<td class=\"table_text\">
$fname $lname ($position) </td>
<td class=\"table_text\">$goals</td>
<td class=\"table_text\">$assists</td>";

if($user_xtra1 == 1)
{
    $stats .= "<td class=\"table_text\">$xtra1</td>";
}

if($user_xtra2 == 1)
{
    $stats .= "<td class=\"table_text\">$xtra2</td>";
}

if($user_xtra3 == 1)
{
    $stats .= "<td class=\"table_text\">$xtra3</td>";
}

if($user_xtra4 == 1)
{
    $stats .= "<td class=\"table_text\">$xtra4</td>";
}

$stats .= "
<td class=\"table_text\">$minutes</td></tr>";
$i++;  
}

The only change you have to do is replace your '0' with '$i' in your mysql_result() function.

Thanks,
Regards,
niroshan

[msnhockey]

worked like a charm..

thanks alot for the help.. I have been trying this for about 12+ hrs.