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!!!
query about mysql statements
Moderator: General Moderators
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
When you've got the ID do a SELECT on the players table:
then you've got the player's name which you can now display.
Mac
Code: Select all
SELECT playername FROM players WHERE ID='playernum'Mac
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!
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!
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
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']);
}