whats wrong with this???

[msnhockey]

wondering what I did wrong in this query

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

I am gathering from the players table

teamid which = $gm_team2
player position
registered

From the player_stats table
playerid which = $pid
game_is which = $id
played which = 1

then order by last name

[NogDog]

I don't see anything obviously wrong, but you might want to do something like the following to get some more debug info.

ini_set('display_errors',1);
error_reporting(E_ALL);
$sql = "SELECT * FROM players, player_stats "
     . "WHERE players.teamid = '".$gm_team2."' AND players.position != 'G' "
     . "AND players.registered = '1' AND player_stats.playerid = '".$pid."' "
     . "AND player_stats.game_id = '".$id."' AND player_stats.played = '1' "
     . "ORDER BY lname";
$result = mysql_query($sql);
if(!$result)
{
   user_error(mysql_error()."<br />\n".$sql);
}
elseif(mysql_num_rows($result) == 0)
{
   user_error("No rows found.<br />\n".$sql);
}

[msnhockey]

the query looks good, but with the way you did things do I have to change something within this code... all I get is white, but no errors

	        while($i < $total_pi) {

$tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";
$pid = mysql_result($result_pi,$i,"id");



            	//GET STAT INFO


$goals = mysql_result($result_pi,0,"goals");
$assists = mysql_result($result_pi,0,"assists");
$goalsa = mysql_result($result_pi,0,"goalsa");
$shotsa = mysql_result($result_pi,0,"shotsa");
$minutes = mysql_result($result_pi,0,"minutes");
$xtra1 = mysql_result($result_pi,0,"xtra1");
$xtra2 = mysql_result($result_pi,0,"xtra2");
$xtra3 = mysql_result($result_pi,0,"xtra3");
$xtra4 = mysql_result($result_pi,0,"xtra4");
$played = mysql_result($result_pi,0,"played");




$stats .= "

<tr bgcolor=\"".$tbgcolor."\" align=\"center\">
<td class=\"table_text\">
".mysql_result($result_pi,$i,"players.fname")." ".mysql_result($result_pi,$i,"lname")."
(".mysql_result($result_pi,$i,"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++;



        }

[msnhockey]

my bad... i get this notice

Notice: No rows found.
SELECT * FROM players, player_stats WHERE players.teamid = '0008' AND players.position != 'G' AND players.registered = '1' AND player_stats.playerid = '0000000330' AND player_stats.game_id = '00000185' AND player_stats.played = '1' ORDER BY lname in /sports/hockey/inc/showgame.php on line 754

this is line 754
754 user_error("No rows found.<br />\n".$result_pi);

[NogDog]

Well, the good news is that the query parsed OK. The bad news is that there may be some logic error, since presumably you expected more that zero rows to have matched. One thing you might need to change is where you have what appear to be numeric columns being compared to quoted string values. If the columns in question are, in fact, integer type columns, then you may need to remove the quotes from around the literal values to which they are being compared. (I can never remember the precise rules and priorities for how values are type-cast when you try to compare different types, so it may not matter, but you can't go wrong by getting rid of the quotes if the column being compared is a numeric type.)

[msnhockey]

just remove ' ' around the numeric stuff..

i will try and then post how i make out

[msnhockey]

just brings up the exact same, but without any quotes

[NogDog]

One thing that looks odd to me (though I'm not sure it would change the current results) is that I don't see how the two tables are related to each other. It seems to me like maybe they should both have a player ID field that would be in common, perhaps? If so, then the WHERE clause would need to have added to it something like:

AND players.playerid = player_stats.playerid

[msnhockey]

yes that is right
in the players table the player id is id
and in the player_stats table the player id is playerid

[msnhockey]

even by adding that code in I still get the no rows found..

[NogDog]

Well, I guess that begs the question, are there any combined rows from the actual data that would, in fact, match your search criteria? 😉

[msnhockey]

nothing more combined. the only other thing i notice is that when calling the
'".$pid."' is the query it goes to this
$pid = mysql_result($result_pi,$i,"id");
which I think in returns looks for the query... i might have to change something there or in the $pid = mysql_result($result_pi,$i,"id"); line

[msnhockey]

here is the code in which i have so far with yours included.

ini_set('display_errors',1); 
error_reporting(E_ALL); 
$sql = "SELECT * FROM players, player_stats " 
     . "WHERE players.teamid = '".$gm_team2."' AND players.position != 'G' " 
     . "AND players.registered = '1' AND players.id = '".$pid."' " 
     . "AND player_stats.game_id = '".$id."' AND player_stats.played = '1' " 
     . "ORDER BY lname"; 
$result = mysql_query($sql); 
if(!$result) 
{ 
   user_error(mysql_error()."<br />\n".$sql); 
} 
elseif(mysql_num_rows($result) == 0) 
{ 
   user_error("No rows found.<br />\n".$sql); 
} 



$total_pi = mysql_numrows($result);


$i = 0;


while($i < $total_pi) {

$tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";
$pid = mysql_result($result,$i,"players_stats.playerid");

$goals = mysql_result($result,0,"player_stats.goals");
$assists = mysql_result($result,0,"player_stats.assists");
$goalsa = mysql_result($result,0,"player_stats.goalsa");
$shotsa = mysql_result($result,0,"player_stats.shotsa");
$minutes = mysql_result($result,0,"player_stats.minutes");
$xtra1 = mysql_result($result,0,"player_stats.xtra1");
$xtra2 = mysql_result($result,0,"player_stats.xtra2");
$xtra3 = mysql_result($result,0,"player_stats.xtra3");
$xtra4 = mysql_result($result,0,"player_stats.xtra4");
$played = mysql_result($result,0,"player_stats.played");




$stats .= "

<tr bgcolor=\"".$tbgcolor."\" align=\"center\">
<td class=\"table_text\">
".mysql_result($result,0,"players.fname")." ".mysql_result($result,0,"players.lname")."
(".mysql_result($result,0,"players.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);
}

[msnhockey]

if i go like this with my code everything shows up, but the alternating row colors are messed up

[COLOR="Red"]$result_pi = mysql_query("SELECT * FROM players WHERE teamid = '".$gm_team2."' AND position != 'G' AND registered = '1' ORDER BY lname");[/color]


        $total_pi = mysql_numrows($result_pi);

        $i = 0;






        while($i < $total_pi) {



                //ROW COLOR

                $tbgcolor = is_int($i / 2)?"#efefef":"#ffffff";

		$pid = mysql_result($result_pi,$i,"id");



            	//GET STAT INFO

	[COLOR="Red"]	$result_ps = mysql_query("SELECT * FROM player_stats WHERE playerid = '".$pid."' AND game_id = '".$id."'");[/color]

		$total_ps = mysql_numrows($result_ps);



		if($total_ps > 0) {

			$goals = mysql_result($result_ps,0,"goals");

			$assists = mysql_result($result_ps,0,"assists");

			$goalsa = mysql_result($result_ps,0,"goalsa");

			$shotsa = mysql_result($result_ps,0,"shotsa");

			$minutes = mysql_result($result_ps,0,"minutes");

			$xtra1 = mysql_result($result_ps,0,"xtra1");

                            $xtra2 = mysql_result($result_ps,0,"xtra2");

                            $xtra3 = mysql_result($result_ps,0,"xtra3");

                            $xtra4 = mysql_result($result_ps,0,"xtra4");
                            $played = mysql_result($result_ps,0,"played");

		} else {

                            $goals = "0";

                            $assists = "0";

                            $goalsa = "0";

                            $shotsa = "0";

                            $minutes = "0";

                            $xtra1 = "0";

                            $xtra2 = "0";

                            $xtra3 = "0";

                            $xtra4 = "0";

                            $played = "0";
		}


if($played == 1) {
			$stats .= "

                <tr bgcolor=\"".$tbgcolor."\" align=\"center\">

                <td class=\"table_text\">"

                .mysql_result($result_pi,$i,"fname")." ".mysql_result($result_pi,$i,"lname")."

                (".mysql_result($result_pi,$i,"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_ps);

        }