Jump to content

help with developing a select menu to display info from two MySQL tables.

webguync

By webguync
in PHP Coding Help

Recommended Posts

webguync Advanced Member

webguync

Posted
Posted

I am working on a project where I want a select form to display information from a MySQL table. The select values will be different sports (basketball,baseball,hockey,football) and the display will be various players from those sports. I have set up so far two tables in MySQL. One is called 'sports' and contains two columns. Once called 'category_id' and that is the primary key and auto increments. The other column is 'sports' and contains the various sports I mentioned. For my select menu I created the following code.

 

<?php
#connect to MySQL
$conn = @mysql_connect( "localhost","uname","pw")
or die( "You did not successfully connect to the DB!" );

#select the specified database
$rs = @mysql_SELECT_DB ("test", $conn )
or die ( "Error connecting to the database test!");
?>

<html>
<head>Display MySQL</head>
<body>
<form name="form2" id="form2"action="" >
<select name="categoryID">
<?php
$sql = "SELECT category_id, sport FROM sports ".
"ORDER BY sport";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['category_id']."\">".$row['sport']."</option>\n  ";
}
?>
</select>

</form>
</body>
</html>

 

this works great. I also created another table called 'players' which contains the fields 'player_id' which is the primary key and auto increments, category_id' which is the foreign key for the sports table, sport, first_name, last_name.

 

The code I am using the query and display the desired result is as follows

 

<html>
<head>
<title>Get MySQL Data</title>
</head>
<body>
<?php
#connect to MySQL
$conn = @mysql_connect( "localhost","uname","pw")
or die( "Err:Db" );

#select the specified database
$rs = @mysql_SELECT_DB ("test", $conn )
or die ( "Err:Db");

#create the query
$sql ="SELECT *
FROM sports
INNER JOIN players ON sports.category_id = players.category_id
WHERE players.sport = 'Basketball'";

#execute the query
$rs = mysql_query($sql,$conn);

#write the data
while( $row = mysql_fetch_array( $rs) )
{
echo ("<table border='1'><tr><td>");
echo ("Caetegory ID:  " . $row["category_id"] );
echo ("</td>");
echo ("<td>");
echo ( "Sport: " .$row["sport"]);
echo ("</td>");
echo ("<td>");
echo ( "first_name: " .$row["first_name"]);
echo ("</td>");
echo ("<td>");
echo ( "last_name: " .$row["last_name"]);
echo ("</td>");
echo ("</tr></table>");
}
?>
</body>
</html>

 

this also works fine. All I need to do is tie the two together so that when a particular sport is selected, the query will display below in a table. I know I need to change my WHERE clause to a variable. This is what I need help with.

 

thanks

webguync Advanced Member

webguync

Posted
  • Author
Posted

I know that is a lot of code. A few more lines I forgot to add. I changed in my form code to

 

<form name="sports" id="sports" action="Query.php" method="post" >

 

my also added in my php code

 

$id=["category_id"];
#create the query
$sql ="SELECT *
FROM sports
INNER JOIN players ON sports.category_id = players.category_id
WHERE players.sport = '.$_GET[$id]'";

 

still doesn't work but I think I am getting close.

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.