Using PHP/MySQL to populate drop down and display results

waynewnewbie

Hey Everyone, I am a way newbie to PHP & MySQL code and I am sure this most likely a very basic question but I am fairly lost here. What I am trying to do is to populate a drop down from a MySQL table then based on what is selected in the drop down some other data from the database will be displayed in a table on the same page. This code is what I have so far. What is happening is that the drop down is being populated OK and the table headers are displaying OK. Yet when I hit submit no matter which record I have selected it only displays the name of the first record in the drop down and it always defaults back to the first name when done.

Also eventually as a separate page I would like for the associated database info to be displayed in a form for editing & updating.

If anyone has some recommendations that would be great. I am not opposed to incorporating Javascript if needed but I am just looking for a solution. Many thanks in advance for your help and patience:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>Untitled Document</title>
	</head>

<body>

	<form id="form1" name="form1" method="post" action="">

<?php 

 $con=mysqli_connect('127.0.0.1','root','','data1_schema');
    //mysql_select_db('');
    $chooserResult = mysqli_query($con,"SELECT agency_num, agency_name FROM contact_data");
 ?>  

 <select name="chooser" id=“chooser”>

      <?php while ($row = $chooserResult->fetch_assoc()): ?>

         <option value="<?php echo $row['agency_num'];?>"><?php echo $row['agency_name']; ?></option>

     <?php endwhile; ?>

    </select>

 <input type="submit" value="Submit" />
</form>
</br>

<table border="1" id="table">
  <tr>
    <th width=80 height=30>Name</th>
    <th width=110 height=30>Street</th>
    <th width=90 height=30>City</th>
    <th width=60 height=30>State</th>
    <th width=60 height=30>Zip</th>
  </tr>

  <?php 
  if($_SERVER['REQUEST_METHOD'] =='POST')
 { $option_chosen=$_POST['chooser'];
     $query="SELECT * FROM contact_data WHERE agency_num='$chooser'";
     $run=mysqli_query($con,"SELECT agency_num, agency_name FROM contact_data");
     $row=mysqli_fetch_array($run, MYSQLI_ASSOC);

echo "<tr><td>".$row['agency_name']."</td>";
echo  "<td>".$row['agency_street']."</td><td>".$row['agency_city']."</td>";
echo "<td>".$row['agency_state']."</td><td>".$row['agency_zip']."</td></tr>";

}
?>

     </table>

</body>
</html>

big_nerd

waynewnewbie,

Welcome to the boards.

Please note you should place your code between code blocks by using [noparse]

...

[/noparse] BB Codes.

First and foremost - I would like you to look at how to Escape using mysqli_real_escape_string, as the code here:

$query="SELECT * FROM contact_data WHERE agency_num='$chooser'";

Can cause problems as it can be compromised using SQL Injection.

If you want the original drop down (select) box to maintain it's position when posting, do something similar to the following:

<?php while ($row = $chooserResult->fetch_assoc()): ?>

<option value="<?php echo $row['agency_num'];?>" <?php if (!empty($_POST['chooser']) && $row['agency_num'] == $_POST['chooser']) echo 'selected'; ?> ><?php echo $row['agency_name']; ?></option>

<?php endwhile; ?>

To Break it down:

In HTML, to tell the browser which item is selected you use it like:

<option value="big.nerd" selected="selected">Big Nerd</option>

What we are doing here is saying "If the agency number has been posted, let's see if it is the same number as the one currently being listed, if it is, let's call this selected".

.. One important thing, I may have an error in the code since I kind of rushed this.. so, work through it, you get the idea.

MOD EDIT: Using [noparse]

 or [code=html] tags, when appropriate, are preferable over the generic [code] tags due to the syntax highlighting.[/noparse] ;)