dessolator Posted November 28, 2007 Share Posted November 28, 2007 Hi, what I'm trying to do is display data from two different mysql tables from the same database in a drop down list on a html form. I have a fixtures table with the player1(userid), player2(userid), gameid, game, score1 and 2, what I want is to use the userid to get the players first name and surname from the members table (as it is a unique id), I need to do this bit before displaying it in the drop down. I think i need 2 querys to do this but when I have tried it it just echo's a blank value or the userid not the forname and surname that I want. I'm using the fetch_array function but just can't see where I'm going wrong, I would really appreciate your help on this, I have attached my code below - sorry about the poor indentation. Thanks, Ian <?php $host="localhost"; // Host name $username="root"; // Mysql username $password="abc123"; // Mysql password $db_name="games_db1"; // Database name $tbl_name="members"; // Table name // Connect to server and select databse. mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select DB"); ?> <body> <form id="form1" name="form1" method="post" action=""> <label>Fixture <select name="select"> <?php echo "<center>"; //Database - Assigns the below statement into the variable query $query = "SELECT GameID, PlayerY, PlayerG, Game FROM results"; $result = mysql_query($query); //$row = mysql_fetch_row($result); while($row = mysql_fetch_array($result)) { $gameid = $row[0]; $yplayer = $row[1]; $gplayer = $row[2]; $game = $row[3]; } $query1 = "SELECT Forename, Surname FROM members WHERE 'id' = $yplayer"; $result1 = mysql_query($quer1y); //$row = mysql_fetch_row($result); while($row = mysql_fetch_array($result)) { $yellowfn = $row[0]; $yellowsn = $row[1]; } echo"<option style='background: yellow' value='$row[0]'>Game ID: $row[0] | Name: '$yellowfn', '$yellowsn' , | Class: $row[3]</option>"; echo "</center>"; ?> </select> <?php ?> </label> <p>Yellow Score: <label> <input type="text" name="textfield" /> </label> </p> <p>Green Score: <label> <input type="text" name="textfield2" /> </label> </p> </form> </body> </html> Quote Link to comment Share on other sites More sharing options...
teng84 Posted November 28, 2007 Share Posted November 28, 2007 $gameid = $row[0]; $yplayer = $row[1]; $gplayer = $row[2]; $game = $row[3]; should be $gameid = $row[GameID]; $yplayer = $row[PlayerY]; $gplayer = $row[PlayerG]; $game = $row[Game ]; Quote Link to comment Share on other sites More sharing options...
dessolator Posted November 28, 2007 Author Share Posted November 28, 2007 Hi, thanks for your reply, It still doesn't like showing the forename and surname and just shows a blank space, but when I put in the uid its fine. Looks like it must be a problem with my query or the bit below: $query1 = "SELECT id, Forename, Surname FROM members WHERE id = $yplayer"; $result1 = mysql_query($quer1y); //$row = mysql_fetch_row($result); while($row = mysql_fetch_array($result)) { $yellowfn = $row[Forename]; $yellowsn = $row[surname]; } echo"<option style='background: yellow' value='$gameid'>Game ID: $gameid | Name: $yellowfn, $yellowsn , | Class: $game</option>"; Thanks, Ian Quote Link to comment Share on other sites More sharing options...
teng84 Posted November 28, 2007 Share Posted November 28, 2007 //Database - Assigns the below statement into the variable query $query = "SELECT GameID, PlayerY, PlayerG, Game FROM results"; $result = mysql_query($query) or die (mysql_error()); print_r($result); //$row = mysql_fetch_row($result); while($row = mysql_fetch_array($result)) { $gameid = $row[0]; $yplayer = $row[1]; $gplayer = $row[2]; $game = $row[3]; } try and tell us what happen Quote Link to comment Share on other sites More sharing options...
dessolator Posted November 28, 2007 Author Share Posted November 28, 2007 Hi, thanks again for your reply, its much appreciated. Its just coming up in the dropdown like this: Game ID: 2 | Name: , , | Class: Spillikins its not even printing the result from the query on the page. Thanks, Ian Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.