URGENT - connecting 2 tables 2 display info!

susi

hi
have a problem.. i have 2 tables players table and members table.. now in the members table i have the username, password, player1 - player11
in the player1 - player11 is the id for the playername which is in the players table..

in the players table is a id, playername, club.

when a member logs in and goes to the members page.. it displays the 11 players that the member picked!

but instead of displaying the name.. its displaying the id!

any ideas how to connect the tables so that when a person logs in using their user/pass the players names will be displayed instead of the id codes!

thanking you in advance!!!

susi

here's more info..... in case it mite help!
im very stuck.. runout of ideas!!!!! 🙁

table players

CREATE TABLE players
(p_id INT PRIMARY KEY,
p_name VARCHAR(20),
p_team VARCHAR(3),
p_value DOUBLE,
weekPts INT,
totalPts INT);

table members

CREATE TABLE members (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
password VARCHAR(12) NOT NULL,
username VARCHAR(12) NOT NULL,
player1 VARCHAR(20) NOT NULL,
player2 VARCHAR(20) NOT NULL,
player3 VARCHAR(20) NOT NULL,
player4 VARCHAR(20) NOT NULL,
player5 VARCHAR(20) NOT NULL,
player6 VARCHAR(20) NOT NULL,
player7 VARCHAR(20) NOT NULL,
player8 VARCHAR(20) NOT NULL,
player9 VARCHAR(20) NOT NULL,
player10 VARCHAR(20) NOT NULL,
player11 VARCHAR(20) NOT NULL,
index(player1),
index(player2),
index(player3),
index(player4),
index(player5),
index(player6),
index(player7),
index(player8),
index(player9),
index(player10),
index(player11));

now... the p_id in the players table goes into the members table when a member selects their 11 players...

eg..
player1 player
12 15

then it displays the code 12 on da members page instead of the name
what i need to do is somehow link the two tables that will display then name instead of the id!!

the select statement im using at the moment to display the players code is

$result = mysql_query("SELECT * from members2 where username = '$SESSION_UNAME'");

$row = mysql_fetch_array($result);

echo $row['player1'];

any suggestions???/ very stuck

thanks in advance! 😕

ir4z0r

not elegant, but a solution :rolleyes:

$result = mysql_query("SELECT * from members2 where username = '$SESSION_UNAME'"); 

$row = mysql_fetch_array($result); 

for ($i = 1; $i < 12; $i++)
{
  $pl = "player" . $i;
  $result2 = mysql_query("SELECT p_name FROM players WHERE p_id=" . $row[$pl];
  $row2 = mysql_fetch_array($result2);
  echo $row2['p_name'];
}

nnichols

Much the same as above, but using 2 queries instead of 12 -

<?

$sql = "SELECT * from members where username = '$SESSION_UNAME'";
$result = @mysql_query($sql)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql); 

while ($row = mysql_fetch_array($result)) {

$sql = "SELECT p_name FROM players WHERE p_id IN ($row['player1'],$row['player2'],$row['player3'],$row['player4'],$row['player5'],$row['player6'],$row['player7'],$row['player8'],$row['player9'],$row['player10'],$row['player11'])";
$result2 = @mysql_query($sql)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql); 

while ($row2 = mysql_fetch_array($result2)) {
	echo $row2['p_name'].'<br>';
}

}

?>

or you could try a multi join query like this -

$sql = <<<EOD
SELECT p1.p_name, p2.p_name, p3.p_name, 
	p4.p_name, p5.p_name, p6.p_name, 
	p7.p_name, p8.p_name, p9.p_name, 
	p10.p_name, p11.p_name
FROM players p1, players p2, players p3,
	players p4, players p5, players p6,
	players p7, players p8, players p9,
	players p10, players p11, members m
WHERE p1.p_id=m.player1
AND p2.p_id=m.player2
AND p3.p_id=m.player3
AND p4.p_id=m.player4
AND p5.p_id=m.player5
AND p6.p_id=m.player6
AND p7.p_id=m.player7
AND p8.p_id=m.player8
AND p9.p_id=m.player9
AND p10.p_id=m.player10
AND p11.p_id=m.player11
AND m.username = '$SESSION_UNAME'
EOD;
$result = @mysql_query($sql)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

Hope this helps 😉

ultraslacker

I would be interested to see how mysql handles that last join.

This difficult sql is what can occur with a denormalized design. In a normalized design it is trivial -

member
member_id
member_name

player
player_id
player_name

member_player
member_id
player_id

SELECT player_name
FROM player p, member m, member_player mp
WHERE member_name = '$username' AND
m.member_id = mp.member_id AND p.player_id = mp.player_id.

nnichols

ultraslacker is definitely right. You will save yourself a lot of trouble by normalizing your data structure.

ultraslacker:

surprisingly, mysql didn't struggle with my join example at all. I tested it with some sample data (only 10,000 players) and it ran ok.

susi

thanks.. its working now! 🙂