Page 1 of 1

PHP/MySQL Drop Down Menu Form question

Posted: Sun Mar 23, 2008 3:15 pm
by obcbeatle
Hello:

I'm new to PHP. I've been reading the PHP & MySql documentation, plus trying the W3C School tutorials for about a week now and I still can't figure out how to write PHP code to populate a drop down menu form via a MySQL db. Any pointers/examples/suggestions much appreciated. In my example I believe this is a (2) step process:

1) Writing the PHP code to populate the drop down menu from a MySQL db.
2) Writing the PHP code that processes what the user selects from the drop down menu (the form action PHP script).

Please correct me if I'm wrong about the process above.

Step 1...here is the code I have so far for populating a drop down menu:

//Making the db connection

<?php
$con = mysql_connect("my_server","uid","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

//Select all baseball player image captions from a MySQL db table

$result = mysql_query("SELECT caption
FROM tbllinkcat, tblimages
WHERE tbllinkcat.catid = tblimages.catid
AND tbllinkcat.catid=3");

//List all baseball players in a form drop down menu

while($row = mysql_fetch_array($result))

***And here is where I'm stuck. I'm not sure what to put after the while statement above to correctly place the fetched records in the array inside a drop down box. The MySQL select statement above works (in phpMyAdmin), and, I've seen examples of writing an of array records in an html form, using echo to call the form method and action, etc. but most of the examples don't make sense to me***

//Close db connection

mysql_close($con);
?>

Step 2...process the record that the user selects from the drop down box. I have written the MySQL select statements, but I'm uncertain how to write the PHP code that runs the SQL statements based on which record the user selects from the drop down box. For instance, if the user selects Ty Cobb from the drop down box I want to run a SQL query that will create a html page that displays all the Ty Cobb db records (images). Or if the user selects Babe Ruth from the drop down box I'll run a different query to display all the Ruth db records, etc.

Again...any examples/comments/suggestions much appreciated. Thanks!

Re: PHP/MySQL Drop Down Menu Form question

Posted: Sun Mar 23, 2008 4:43 pm
by flying_circus
Your while statement needs to produce some output for the database records your are stepping through. For example:

Code: Select all

# Code for Step 1
<form action="step2.php" method="POST">
  <select name="playerName">
  <?php
    while($row = mysql_fetch_array($result)) {
      print '<option value="' . $row['caption'] . '">' . $row['caption'] . '</option>';
    }
  ?>
  </select>
</form>
The above code should populate your HTML form.

The next step is to write the code for step 2. The page for Step 2 is defined in your <form> element from step 1. The forms "action" property should point to step2.php and the method should be "POST".

Code: Select all

# Code for Step 2
<?php
  $playerName = $_POST['playerName'];
 
  // $res = mysql_query("SELECT baseBallPlayerName FROM players WHERE playersName = $playerName");
  // $singleRow = mysql_fetch_array($res);
  
?>
This should get you started. Since I cant tell the structure of your db or the rest of your page's architecture, you may need to modify it to fit your needs.

Re: PHP/MySQL Drop Down Menu Form question

Posted: Mon Mar 24, 2008 7:10 pm
by obcbeatle
Thanks for the reply and assistance! Indeed your code did work for me and I think I now understand how to use print or echo to produce db output. Below is the page that now gets populated with a drop down list per my MySQL db. Unfortunately, I haven't been able to get the step2.php (action property script) to work. I think it is because I don't understand how to link to it properly. Seems like the select name (playerName) may not be getting passed once I close the db, because PHP can't find it? I'm not sure if that's correct or if that even makes sense. I've also included my current step2.php action script below too.

Here's the page that successfully gets a drop down list populated by a db:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"
"http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>A Title</title>
<link rel="stylesheet" href="style_sheet.css" type="text/css" media="screen">
</head>
<body>
<div id="container">
<h1>A Header</h1>
<div id="nav">

<?php require("php/main_links.php"); ?>

</div>
<div id="content">
<h2>
Another Header
</h2>
<p>
Some text
</p>

<form action="php/step2.php" method="POST">
<select name="playerName">

<?php
$con = mysql_connect("server","uid","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT caption
FROM tbllinkcat, tblimages
WHERE tbllinkcat.catid = tblimages.catid
AND tbllinkcat.catid=3
ORDER BY caption");

while($row = mysql_fetch_array($result)) {
print '<option value="' . $row['caption'] . '">' . $row['caption'] . '</option>';
}

mysql_close($con);
?>

</select>
<input type="submit" />
</form>

</div>

<div id="footer">
A footer
</div>
</div>
</body>
</html>

Here's my current step2.php script located in my ~php directory. First, I'm opening the db again so that the mysql_query will work. Then I'm trying to pass the select name "playerName" into the mysql_query SQL statement (I think this is where I'm messing up). Finally I'm trying to step thru the array and output the data using echo. BTW, caption is a field in the db table that has values that look like this (Ty Cobb Detroit Tigers 1908), and image is a field that contains links to images:

<?php

$con = mysql_connect("server","uid","pw");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$playerName = $_POST['playerName'];

$res = mysql_query("SELECT image, caption FROM tblimages WHERE caption = $playerName");

while($row = mysql_fetch_array($res))
{
echo $row['image'];
echo "<br />";
echo $row['caption'];
echo "<br />";
}

mysql_close($con);

?>

Re: PHP/MySQL Drop Down Menu Form question

Posted: Mon Mar 24, 2008 10:47 pm
by flying_circus
I believe the problem lies with your SQL query in step2.

In your SQL query, try wrapping $playerName in single quotes:

Code: Select all

 
$res = mysql_query("SELECT image, caption FROM tblimages WHERE caption = '$playerName'");
 
A better practice would be to do your select dropdown list a little differently. This would be a much cleaner solution and much more predictable, assuming that you've got a primary key field with a name of "id":

Code: Select all

<?php
$result = mysql_query("SELECT id,caption
FROM tbllinkcat, tblimages
WHERE tbllinkcat.catid = tblimages.catid
AND tbllinkcat.catid=3
ORDER BY caption"); 
 
while($row = mysql_fetch_array($result)) {
print '<option value="' . $row['id'] . '">' . $row['caption'] . '</option>';
}
 
?>
On your second page, it is easier to write a query the looks for an id of an integer in the where clause, rather than a caption thats a string and may contain things like spaces or quotes that will cause your query to fail.

Let me know if you dont understand, I feel like I am having a hard time communicating what is going on in my brain :?

Re: PHP/MySQL Drop Down Menu Form question

Posted: Wed Mar 26, 2008 6:17 pm
by obcbeatle
You are correct. Wrapping the single quotes around $playerName worked. Thanks so much for the reply! Also, using an integer instead on of a string in the WHERE clause does make sense. I do have an imageID primary key, so I'll give that a try. Thanks for taking the time to look at the code and pointing out the missing quotes, and, for making the integer vs string suggestion!