combine querys

[msnhockey]

wondering what is the easiest way to combine these 2 queries..... I have been trying almost everything and can't get it to work

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

$result_ps = mysql_query("SELECT * FROM player_stats WHERE playerid = '".$pid."' AND game_id = '".$id."'");

I know it needs to be something like this

$result_ps = mysql_query("SELECT * FROM player_stats, players WHERE

then after that is where i get lost

[jeremyphphaven]

Here's what I've come up with. NOTE: I couldn't check the syntax, so if you can use soemthing like HEIDISQL to test the query first, do that!

$query = "select p.*,ps.* from players p left join player_stats p on p.id=ps.playerid where p.teamid='".$gm_team2."' and p.position<>'G' and p.registered='1' and ps.game_id='" .$id. "' and ps.playerid='".$pid."' order by p.lname";
$result = mysql_query($query) or die(mysql_error());

Now, I made some assumptions. those being that you have a primary key in the players table and that primary key is id.

Also, another assumption is that the primary key of id in the players table is the same id as playerid in the player_stats table.

If these assumptions are correct, use that query and you should be good to go.

[msnhockey]

unfortunately i can't get this to work...

[jeremyphphaven]

:glare:

What's your database structure?

[msnhockey]

i think I know what i did wrong...

I forgot to change some of the queries and take some out

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

        $total_pi = mysql_numrows($result_pi);

        $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($i < $total_pi) {



                //ROW COLOR


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



            	//GET STAT INFO
		$result_ps = mysql_query("SELECT * FROM player_stats WHERE playerid = '".$pid."' AND game_id = '".$id."'");


		$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 > 0) {

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

        }

[msnhockey]

with your query you came up with. when calling that query would i need to put it like this
$goals = mysql_result($query,0,"ps.goals");

[msnhockey]

with using your query i get the following erroe

Not unique table/alias: 'p'

[rsmith]

Try this:

$query = "SELECT * FROM `players`, `player_stats` WHERE 1 = 1 AND(teamid.players = '".$gm_team2."' AND position.players != 'G' AND playerid.playerstats = '"$.pid."' AND game_id.playerstats = '".$id."') ORDER BY `lname`";
$result = mysql_query($query):
$info = mysql_fetch_assoc($result);