Jump to content

Having another mare with joining 2 tables!


Kristoff1875
Go to solution Solved by Barand,

Recommended Posts

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!

Link to comment
Share on other sites

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?!

Link to comment
Share on other sites

  • Solution

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.