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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hmm... In this example, are the teams all supposed to have the same t_ID value?
ASKER
Negative. (Sorry about that... terrible sample)
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?
ASKER
Was able to figure this out...
Sorry for the delay on getting back. I will apply points.
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)Sorry for the delay on getting back. I will apply points.
ASKER