Separating data

La_Parka

How would I go about separating this data from a DB:

Players
playerid || fname || lname || teamid
1 || Keith || Van Horn || 16
2 || Allan || Houston || 16
3 || Jason || Kidd || 15
4 || Charlie || Ward || 16
5 || Kenyon || Martin || 15

Teams
teamid || city || nickname
15 || New Jersey || Nets
16 || New York || Knicks

so, using PHP, it is separated by team, and the team name is displayed above the first player from each team, sort of like how vBulletin displays forums and their parent categories on the main index page, e.g.:

New Jersey Nets
Jason Kidd
Kenyon Martin

New York Knicks
Keith Van Horn
Allan Houston
Charlie Ward

sid

thats a simple join:

$team = 16; //whatever team you want

$queryString = "SELECT *
FROM players p, teams t
WHERE p.teamid = t.teamid
AND t.teamid = " . $team;

//and now ask the database for the data...

La_Parka

Originally posted by sid
thats a simple join:

$team = 16; //whatever team you want

$queryString = "SELECT *
FROM players p, teams t
WHERE p.teamid = t.teamid
AND t.teamid = " . $team;

//and now ask the database for the data...

[/B]

But would this loop for each team? Here is another example of how I am looking to display players and teams: http://www.antsports.com/smd/SMD817/rosters.asp

La_Parka

Here is another example that should clarify what I am looking to do:

La_Parka

Anybody?

sid

i could tell you how to do it in oracle. with mysql i would just use 2 statements: loop through the teams first (SELECT FROM teams...) and within that loop grab the player infos (SELECT FROM players WHERE teamid = $team[teamid]...) - or something like that...

La_Parka

Originally posted by sid
i could tell you how to do it in oracle. with mysql i would just use 2 statements: loop through the teams first (SELECT FROM teams...) and within that loop grab the player infos (SELECT FROM players WHERE teamid = $team[teamid]...) - or something like that...

I think that it can be done with a single query, though, and is more dependent on the PHP code for the formatting. Check out the example using MySQL and Perl at the bottom of this page: http://www.onlamp.com/pub/a/onlamp/excerpt/mysqlckbk/index3.html

osusano

this is already correct..

$team = 16; //whatever team you want 

$queryString = "SELECT * 
FROM players p, teams t 
WHERE p.teamid = t.teamid 
AND t.teamid = " . $team;

but based on your screen shot do it like this

$queryString = "SELECT * FROM players p, teams t WHERE p.teamid = t.teamid order by teamid";

and check from your query if the teamid is same so that it will diplay on the same table or else do make another table or column from your html

osusano

bye the way it will give u an ambigous error

try itl ike this

$queryString = "SELECT * FROM players p, teams t WHERE p.teamid = t.teamid order by players.teamid";