Newbie need help with query

[ShopMAster]

Hey guys, I'm a newbie when it comes to MySQL and PHP but I'm learning.

Anyways I have run into a slight problem and I know its easy I just can't put my inger on it.  Here it is:

I have 3 tables in a database. They are called leagues, teams, and player.  A league consist of 6 teams and how I defined it in the league table is team1, team2, team3, team4, team5, and team6, as integers that contain the team id.  In the team table I have name, and leagueid defined, and in the player table I have name, wins, losses, and player_team (which = the name of one of the teams).

So my question is how do I list all the players in a specific league.  I have tried everything.

I thought this would do it but it didn't.  I now this is wrong but I've tried others.:

  [code]<?php
    $bg = '#eeeeee'; //set background color for row


    $teams = mysql_query("SELECT name FROM teams WHERE leagueid = $lgid ");
    $res = mysql_query("SELECT * FROM players WHERE player_team = '" . $teams[name] ."' ");

                            while ( $row = mysql_fetch_row($res) )
                            {
                             
                              $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');  //switch background color
                       
                          echo'<tr bgcolor="'.$bg.'"><td align=center><font face=Verdana size=2>' .$row[1] .'</a></font></td></tr>';                                 

                            }
                           
                           
                            if ( mysql_num_rows($res) == 0 )
                                echo 'There are no players assign to teams as of yet.</td>';
                            else
                            {
                                echo '</table>';
                            }
?>
[/code]

Can anyone assist?

Also how would you set up the structure of the tables to work better?

[paul2463]

[code]
$teams = mysql_query("SELECT name FROM teams WHERE leagueid = $lgid ");
while ($row_teams = mysql_fetch_assoc($teams))
{
$name = $row_teams['name'];
$res = mysql_query("SELECT * FROM players WHERE player_team = '$name' ");

                            while ( $row = mysql_fetch_row($res) )
                            {
                               
                               $bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');  //switch background color
                         
                           echo'<tr bgcolor="'.$bg.'"><td align=center><font face=Verdana size=2>' .$row[1] .'</a></font></td></tr>';                                   

                            }
                           
                           
                            if ( mysql_num_rows($res) == 0 )
                                echo 'There are no players assign to teams as of yet.</td>';
                            else
                            {
                                echo '</table>';
                            }
}
?>
[/code]

[paul2463]

just realised it will print out

if ( mysql_num_rows($res) == 0 )
                                echo 'There are no players assign to teams as of yet.</td>';
                            else

for each team with no players

[paul2463]

you could always try a join in the sql
[code]
$players = mysql_query("SELECT * FROM (teams LEFT JOIN players ON player_team=idteam) WHERE leagueid = $lgid ");
[/code]

[ShopMAster]

paul,

  Thanks a bunch man, I now have the complete list, but its displaying it wrong, where the first team has alternate shade, then the others don't.  It's probably some html code I need to fix.  Thanks a bunch.

[ShopMAster]

[quote author=paul2463 link=topic=122802.msg506917#msg506917 date=1169048530]
you could always try a join in the sql
[code]
$players = mysql_query("SELECT * FROM (teams LEFT JOIN players ON player_team=idteam) WHERE leagueid = $lgid ");
[/code]
[/quote]

I'll try that also.  Thanks.  Where would I put this in my code?