PHP, MySQL results with Primary Keys in a WHILE loop

Experts-Exchange Team,

I have a team generation tool for a small PHP application.  This application has 1 MySQL Database with 2 tables.  The teams are made up of 1 player from each "p_Class".  The tables are...

PLAYERS
p_ID     |     p_Class     |     p_Name    
   1       |          A          |     Player 1    
   2       |          A          |     Player 2    
   3       |          A          |     Player 3    
   4       |          A          |     Player 4    
   5       |          A          |     Player 5    
   6       |          B          |     Player 6    
   7       |          B          |     Player 7    
   8       |          B          |     Player 8    
   9       |          B          |     Player 9    
   10     |          B          |     Player 10    
   11     |          C          |     Player 11    
   12     |          C          |     Player 12    
   13     |          C          |     Player 13    
   14     |          C          |     Player 14    
   15     |          C          |     Player 15    


TEAMS
t_ID     |     p_TeamName     |     t_a     |     t_b     |     t_c    
   1      |      Team Name 1    |      2       |      7       |      12    
   1      |      Team Name 2    |      1       |      9       |      14    
   1      |      Team Name 3    |      5       |      8       |      13    
   1      |      Team Name 4    |      4       |     10      |      11    
   1      |      Team Name 5    |      3       |     10      |      15    
   1      |      Team Name 6    |      5       |      9       |      11    


I want to be able to write a WHILE loop that will list all of the registered teams to display in an HTML Table with...

TEAM NAME     |     PLAYER A     |     PLAYER B     |     PLAYER C    
Team Name 1   |        Player 2       |       Player 7       |     Player 12    
Team Name 2   |        Player 1       |       Player 9       |     Player 14    
Team Name 3   |        Player 5       |       Player 8       |     Player 13    
Team Name 4   |        Player 4       |       Player 10     |     Player 11    
Team Name 5   |        Player 3       |       Player 10     |     Player 15    
Team Name 6   |        Player 5       |       Player 9       |     Player 11    


Is this possible w/ PHP?
NCollinsBBPAsked:
Who is Participating?
 
Ray PaseurCommented:
Yes, it's quite possible.  The usual design for something like this is a "junction table" that has two columns - the team key and the player key.  That is how you get the "relational" in "relational data base design."  There is a many-to-many relationship between teams and players.  Of course it will not always have "many" relationships on both sides, but there is no restriction about the number of teams a player could be on, not the number of players a team might have on the roster.
0
 
NCollinsBBPAuthor Commented:
That's where I'm having the issue.  Not sure how to join the tables.  Is it easier to generate a view in PHPMyAdmin then just query those those fields?
0
 
Ray PaseurCommented:
Hmm... In this example, are the teams all supposed to have the same t_ID value?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NCollinsBBPAuthor Commented:
Negative.  (Sorry about that... terrible sample)
0
 
Ray PaseurCommented:
OK, can you give us a simple "Red-Green-Blue" example and a web URL where the test data and the suspect scripts are available?
0
 
NCollinsBBPAuthor Commented:
Was able to figure this out...

 
SELECT t.t_TeamName AS 'TEAM NAME', P1.p_Name AS 'PLAYER A', P2.p_Name AS 'PLAYER B', P3.p_Name AS 'PLAYER C'
FROM TEAMS t
left join PLAYERS P1 on (t.t_a = P1.p_ID)
left join PLAYERS P2 on (t.t_b = P2.p_ID)
left join PLAYERS P3 on (t.t_c = P3.p_ID)

Open in new window


Sorry for the delay on getting back.  I will apply points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.