Having another mare with joining 2 tables!

[Kristoff1875]

I discussed this http://forums.phpfreaks.com/topic/277939-show-result-from-one-table-where-ids-match-on-another/ the other day, which Barand very kindly helped me solve...

 

Now I have a new problem, with the same kind of issue. In addition to the tables mentioned, I have a "Squads" table. In this table, it has related values from the Players table. I have columns "Player1", "Player2" and so on up to 18. The first table column is related to the User's ID and the rest are player ID's that they've chosen.

 

What I need now is for my search to return every column from 'Squads' where the 'TeamID' ties up, but I also need the player's name from the 'Players' table so that I can use it like so:

 

$PlayerX being Player1, Player2, Player3 columns that have number values related to PlayerID in Player's table...

 

Where $PlayerX from Squads == $PlayerID display $PlayerName

 

I can get all of the player ID's to echo correctly, just not with the names. My last unsuccessful attempt was:

SELECT s.*, p.Surname, p.PlayerID
FROM Squads s
LEFT JOIN Players p
    ON p.PlayerID
WHERE TeamID = '$TeamID'";

Please tell me it's possible?!

 

Many thanks!

[Barand]

Simple answer to this one.

 

Normalize your data so instead of columns player1, player2,...,player18 you have those player values in a separate table, 1 per row.

 

When you've done that, ask again.

[Kristoff1875]

I already have the players all stored in the Players table, but now these values are linking to a user's selection of players from the Players table. Would I need a new table for each user?

[Barand]

You've mentioned squads and players so far. Where does the user enter the picture?

 

Does each squad belong to a user?

[Kristoff1875]

Yep, each squad belongs to a user, sorry, I messed up my question and didn't ask it very clearly!

 

I've solved it with this:

     <?php
                include("connect.php");
				$query  = "SELECT * FROM Squads WHERE TeamID = '4123'
				";
				$result = mysql_query($query);
				while($row = mysql_fetch_array($result, MYSQL_ASSOC))
				{
					
        foreach ($row as $col => $val) {
 			$query1  = "SELECT * FROM Players WHERE PlayerID = '$val'";
				$result1 = mysql_query($query1);
				while($row1 = mysql_fetch_array($result1, MYSQL_ASSOC))
				{
					echo '<li><div class="playerhomeholder"><div class="player">'.$row1['Surname'].'</div></div></li>';
				}
        }
    }				    
?>

but i'm not sure if this is good practice?!

[Barand]

Running queries inside loops kills performance

 

If you normalize as I suggested you need only a single query

+------------+
|  user      |
+------------+            +------------+                                   +-----------+
|  userid    | ---+       |  squad     |                                   | player    |
|  username  |    |       +------------+                                   +-----------+
|  etc       |    |       |  teamid    | --+                           +-- | playerid  |
+------------+    +-----< |  userid    |   |     +-----------------+   |   | playername|
                          |  teamname  |   |     | squadplayers    |   |   +-----------+
                          +------------+   |     +-----------------+   |
                                           +---< |  teamid         |   |
                                                 |  playerid       | >-+
                                                 +-----------------+
                                                 
SELECT p.playername
FROM player p
  INNER JOIN squadplayers sp USING (playerid)
  INNER JOIN squad s USING (teamid)
WHERE s.teamid = $team

[Kristoff1875]

Thanks so much for your advice. I'm actually starting to understand it a little better now! At first the extra table made no sense, but now I've put it all in the database, I can see where it links together! Thanks again!