Page 1 of 1

query about mysql statements

Posted: Sat Mar 22, 2003 7:03 am
by 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!!!

Posted: Sat Mar 22, 2003 7:40 am
by twigletmac
When you've got the ID do a SELECT on the players table:

Code: Select all

SELECT playername FROM players WHERE ID='playernum'
then you've got the player's name which you can now display.

Mac

Posted: Sat Mar 22, 2003 8:35 am
by susi
am.. that doesn't work! i think it is because of the way i created my tables....

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! :wink:

Posted: Sat Mar 22, 2003 11:35 am
by twigletmac
Well if $row['player1']...$row['player11'] are the p_id's then you should be able to do something like

Code: Select all

for ($i = 1; $i <=11; $i++) {
    $sql_in_criteria[] = $row['player'.$i];
}
$sql_in_criteria = "'".implode("', '", $sql_in_criteria)."'";
$sql = "SELECT p_name AS name FROM players WHERE p_id IN($sql_in_criteria)";
$player_query = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
while ($player_row = mysql_fetch_assoc($player_query)) {
    echo stripslashes($player_row['name']);
}