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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.