Autopopulating Select field using PHP and mySQL

Hi, this is an issue that a lot of people have come across and apparently there are fixes using Jquery, Json and all sorts of things- but my specific need is a bit different and I can’t find any article that addresses it exactly.

So I basically have two dropdowns (selects) in a form, let’s call them Category and Name. Category has a list of values you can pick, say Supplier, Client, Associate etc. Now, each value corresponds to a table in the mySQL db, so there’s a table called Suppliers, one called Clients and so on.

What I need is: when the user picks a value from Category, the Select / dropdown called “Name” autopopulates with a list of names from that table. So if the user selected Associates as the category, when he or she opens the second dropdown, there are the list of Associates’ names already there for them to pick from, taken from the appropriate database table.

The reason I’m doing this is so that the user can’t simply add in arbitrary values for Name (e.g if it was just a normal text input), the Name has to be picked from the values in the appropriate table.

Anyone know how this can be done, bearing in mind I’m connecting to multiple mySQL tables here?

Thanks

Using PHP, you would have to reload the page or send to a new page. You can use Javascript to change it using Ajax or by loading the options into a Javascript function. Either way, php cannot change a loaded page without loading it again.

So exactly what Javascript would I use to change it using Ajax?

It sounds pretty complicated for what should be quite easy, all I want to do is have a different query run and pull data into a SELECT based on the value of a preceding SELECT?

So if user selects x, run query y etc.?

AJAX Introduction

That’s a tutorial about Ajax. What you would do would set the onchange attribute of the first select menu to call a page that would return the new set of options and fill out the second select menu.

If you don’t want to learn Javascript enough to do that, you could have the first select menu by itself and have them submit it to a new page that would have the second select.

Hi, thoughtfully posed questions I like.

You would learn a lot by looking at the airline booking sites.

There you pick the airport you are flying from and the second select shows the destinations from that airport.

Generally they do this on the fly by load ALL the options as JS arrays, so while it takes a second or two to load - after that it is very very responsive and a great usablity win.

see: BA, Ryanair, Easyjet (in the UK) for examples - and probably much of the JS code you need.

If your case makes this impossible (say you have literally thousands of ‘names’) then you can use Ajax to go back to the server and get the list for the second select.

Thanks, well from various examples and help I got this far, but it still isn’t working (second select stays blank)

Well, this is what I have now in the page header:


<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

<script type="text/javascript">
	$(function(){
	$("select[name='_Category']").change(function(){
	var selected = $(this).val();
	$("select[name='_Name']").empty().load("getrowforselect.php?category="+selected);
          });
      }); 
</script>

And this is my edited PHP, getrowforselect.php (I added my connection string as I would assume that’s needed):


<?php 

include ('inc/dbconnect.php');

$table_name = mysql_real_escape_string($_GET['category']);

$sql = "SELECT Name FROM ".$table_name." ORDER BY Name ASC";

$rs = mysql_query($sql);                             

while($row = mysql_fetch_array($rs)){                 
        echo "<option value = '".$row['Name']."'>".$rs['Name']."</option>";                  
}        
?> 

This is the HTML:


<tr>
        <td width="300">Category</td>
        <td width="300">
        <select name="_Category" size="1"><option selected>Clients</option><option>Directors</option><option>Employees</option><option>Subcontractors</option><option>Suppliers</option></select>
        </td>
        </tr>
  		<tr>        
        <td width="300">Name</td>
        <td width="300">
        <select name="_Name" size="1"></select>
        </td>
        </tr> 

But it’s still not working- when I pick a value from Category, nothing happens to the Name select…

Any ideas?

Think I might have got somewhere with it now. Thanks for the tips / code- appreciated.