Urgent Help

[susi]

hi...
i've created a football website! when a person registers with the site.. they pick user/pass and 11 players!
this is then sent to database.
when they log in.. the 11 players are displayed on the sreen.. but
instead of the name.. its using the number code i gave it!.. how would i go about displaying the actual name..
sample code as follows.
the registration form

<td width="123" height="19" bgcolor="#C0C0C0"><div align="left"><p>
<SELECT NAME="player1" size=1>
<OPTION SELECTED VALUE=0>- Select Goalkeeper -</OPTION>
<OPTION value=101>&nbsp;D Seaman (Ars) 4.0m</OPTION><OPTION
value= "player1" >&nbsp;G. Warmuz (Ars) 3.0m</OPTION>

what goes into the database is the code above 101 for D Seaman..
then 101 is displayed to the screen

i have a database of the players with
101 D Seamen

How do i link my members database to my players database so that the player name is displayed instead of the code number..

not sure if i made myself clear on what im looking for??
any ideas??
thanking you in advance!!!

[nicc777]

It's difficult to help if we don't have any knowledge of your DB structure. I am taking a shot in the dark here, and hopefully this will help you somewhat. Here goes...

First, here is some test tables and data to illustrate the solution ( a possible solution ):

#
# Table structure for table `t_players`
#

DROP TABLE IF EXISTS `t_players`;
CREATE TABLE `t_players` (
`playerid` int(10) unsigned NOT NULL auto_increment,
`name` varchar(32) NOT NULL default '',
`surname` varchar(32) NOT NULL default '',
PRIMARY KEY (`playerid`)
) TYPE=MyISAM;

#
# Dumping data for table `t_players`
#

INSERT INTO `t_players` (`playerid`, `name`, `surname`) VALUES (101, 'name01', 'surname01'),
(102, 'name02', 'surname02'),
(103, 'name03', 'surname03');
# --------------------------------------------------------

#
# Table structure for table `t_userplayers`
#

DROP TABLE IF EXISTS `t_userplayers`;
CREATE TABLE `t_userplayers` (
`username` char(16) NOT NULL default '',
`playerid` int(11) NOT NULL default '0'
) TYPE=MyISAM;

#
# Dumping data for table `t_userplayers`
#

INSERT INTO `t_userplayers` (`username`, `playerid`) VALUES ('username01', 102),
('username02', 101),
('username02', 103);
# --------------------------------------------------------

#
# Table structure for table `t_users`
#

DROP TABLE IF EXISTS `t_users`;
CREATE TABLE `t_users` (
`username` char(16) NOT NULL default '',
`password` char(32) NOT NULL default '',
PRIMARY KEY (`username`)
) TYPE=MyISAM;

#
# Dumping data for table `t_users`
#

INSERT INTO `t_users` (`username`, `password`) VALUES ('username01', MD5( 'password' )),
('username02', MD5( 'testpassword' ) );

----- /end

Now the MySQL to get everything:

mysql> SELECT a.username,CONCAT( b.name, " ", b.surname ) AS Player_Name FROM t_users AS a, t_players AS b, t_userplayers AS c WHERE a.username = c.username AND b.playerid = c.playerid;
+------------+------------------+
| username | Player_Name |
+------------+------------------+
| username01 | name02 surname02 |
| username02 | name01 surname01 |
| username02 | name03 surname03 |
+------------+------------------+
3 rows in set (0.09 sec)

For a specific username:

mysql> SELECT a.username,CONCAT( b.name, " ", b.surname ) AS Player_Name FROM t_users AS a, t_players AS b, t_userplayers AS c WHERE a.username = c.username AND b.playerid = c.playerid AND a.username = 'username02';
+------------+------------------+
| username | Player_Name |
+------------+------------------+
| username02 | name01 surname01 |
| username02 | name03 surname03 |
+------------+------------------+
2 rows in set (0.01 sec)

Hope that points you in the right direction

Cheers