[RESOLVED] Dependent select boxes with PHP & MySQL

Omri16

Hello all,

I want to do something that seems very simple but it's a bit hard to explain, so I'll just give you an example.

Suppose you have a MySQL database with 2 tables -
Cars (id, name)
Models (id, name, car_id)
The models include a reference to the car they belong to. For example, if the table "Cars" has 2 rows - (1,Mercedes) and (2,Volkswagen), then an example for a model that belongs to Mercedes in the "Models" table would be (1,E-Class,1), the latter 1 being the id of Mercedes.

My problem is this: I want to have 2 select boxes, one representing the car and the other representing the model. But, the point is that when the user changes his car selection, the other select box changes automatically so as to include only models that belong to the selected car.

I hope you understood this annoying explanation :-)

Thanks for your help!

Weedpacket

Sounds like what you're after are "dynamic dropdowns". Searching these forums for those words should turn something up.

Omri16

Thanks a lot!

Using the term "dynamic dropdown", I found this great website:
http://javascript.about.com/library/bldydrop1.htm
It's simple and easy.

For people who may look for that in the future, here's my PHP implementation of that code (using the database I explained in the first message):

<?
$mysql_link = mysql_connect(DBHOST,DBUSER,DBPASS) or die ("can't connect to mysql");
$mysql_select = mysql_select_db(DBNAME,$mysql_link) or die ("can't select db");
?>

<html>
<head>
<title>Dynamic Dropdown</title>
<script language="javascript">
function setOptions(chosen) {
  var selbox = document.myform.selectmodel;

  selbox.options.length = 0;
  if (chosen == "0") {
    selbox.options[selbox.options.length] = new Option('First select a car','0');

  }
  <?
  $car_result = mysql_query("SELECT * FROM cars") or die(mysql_error());
  while(@($c=mysql_fetch_array($car_result)))
  {
  ?>
    if (chosen == "<?=$c['id'];?>") {

<?
$c_id = $c['id'];
$mod_result = mysql_query("SELECT * FROM models WHERE car='$c_id'") or die(mysql_error());
while(@($m=mysql_fetch_array($mod_result)))
{
?>
  selbox.options[selbox.options.length] = new
  Option('<?=$m['name'];?>','<?=$m['id'];?>');
<?
}
?>
}
  <?
  }
  ?>
}
</script>
</head>
<body>
<form name="myform"><div align="center">
<select name="selectcar" size="1"
onchange="setOptions(document.myform.selectcar.options
[document.myform.selectcar.selectedIndex].value);">
<option value="0" selected>Select a car</option>
<?
$result = mysql_query("SELECT * FROM cars") or die(mysql_error());
while(@($r=mysql_fetch_array($result)))
{
?>
  <option value="<?=$r['id'];?>"><?=$r['name'];?></option>
<?
}
?>
</select><br><br>
<select name="selectmodel" size="1">
<option value=" " selected>First select a car</option>
</select><br><br>
<input type="button" name="go" value="Value Selected"
onclick="alert(document.myform.selectmodel.options
[document.myform.selectmodel.selectedIndex].value);">
</div></form>
</body>
</html>

bbmak

Thank you so much.
I have used your drop down menu for awhile, and it works great. However, I have encounter another problem. when I query subcategory from my database which I already selected. I cannot display the full list in the database.

Here is a picture, you see only the selected subcategory showed, but not everything on the listed.
[ATTACH]4839[/ATTACH]

Here is another picture of the full list of subcategory
[ATTACH]4841[/ATTACH]

I wonder do you have any idea how you solve this problem?

Thank you in advance,

Omri16;10939028 wrote:

Thanks a lot!

Using the term "dynamic dropdown", I found this great website:
http://javascript.about.com/library/bldydrop1.htm
It's simple and easy.

For people who may look for that in the future, here's my PHP implementation of that code (using the database I explained in the first message):

<?
$mysql_link = mysql_connect(DBHOST,DBUSER,DBPASS) or die ("can't connect to mysql");
$mysql_select = mysql_select_db(DBNAME,$mysql_link) or die ("can't select db");
?>

<html>
<head>
<title>Dynamic Dropdown</title>
<script language="javascript">
function setOptions(chosen) {
  var selbox = document.myform.selectmodel;

  selbox.options.length = 0;
  if (chosen == "0") {
    selbox.options[selbox.options.length] = new Option('First select a car','0');

  }
  <?
  $car_result = mysql_query("SELECT * FROM cars") or die(mysql_error());
  while(@($c=mysql_fetch_array($car_result)))
  {
  ?>
    if (chosen == "<?=$c['id'];?>") {

<?
$c_id = $c['id'];
$mod_result = mysql_query("SELECT * FROM models WHERE car='$c_id'") or die(mysql_error());
while(@($m=mysql_fetch_array($mod_result)))
{
?>
  selbox.options[selbox.options.length] = new
  Option('<?=$m['name'];?>','<?=$m['id'];?>');
<?
}
?>
}
  <?
  }
  ?>
}
</script>
</head>
<body>
<form name="myform"><div align="center">
<select name="selectcar" size="1"
onchange="setOptions(document.myform.selectcar.options
[document.myform.selectcar.selectedIndex].value);">
<option value="0" selected>Select a car</option>
<?
$result = mysql_query("SELECT * FROM cars") or die(mysql_error());
while(@($r=mysql_fetch_array($result)))
{
?>
  <option value="<?=$r['id'];?>"><?=$r['name'];?></option>
<?
}
?>
</select><br><br>
<select name="selectmodel" size="1">
<option value=" " selected>First select a car</option>
</select><br><br>
<input type="button" name="go" value="Value Selected"
onclick="alert(document.myform.selectmodel.options
[document.myform.selectmodel.selectedIndex].value);">
</div></form>
</body>
</html>

dropdown.png dropdown2.png