how to populate a combobox from mysql database and then populate a second and third combobox based on selection from first and second combobox (using php and mysql database

chefmgbrown
chefmgbrown used Ask the Experts™
on
I have three tables
manufacturers (id, manuf_name)
cigar_lines(line_id,line_name, id) id is foreign key from manufacturer table
details(detail_id,details,line_id) line_id is foreign key from cigar_lines table

The first combobox needs to get values from the manufacturers table. I want the user to select a manufacturer, have the second combobox populate with all the cigar lines made from that manufacturer(line_name), and then the third combobox populate with all the cigars within the chosen line(details). Any help would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008
Commented:
Change the db connection parameters and test this code:
<?php
 
mysql_connect('localhost','user','pass');
mysql_select_db('dbname');
 
function dropdown($name,$id,$display,$selected,$query) {
  echo '<select name="'.$name.'" onchange="this.form.submit()">'."\n";
  echo '<option>Choose...</option>'."\n";
  $res = mysql_query($query);
  if(!$res) die(mysql_error());
  $was_selected = false;
  while($row = mysql_fetch_assoc($res)) {
    echo '<option value="'.$row[$id].'"';
    if($row[$id]==$selected) {
      echo ' selected="selected"';
      $was_selected = true;
    }
    echo '>'.$row[$display].'</option>'."\n";
  }
  echo '</select>'."\n";
  return $was_selected;
}
 
echo '<form method="post">';
 
$manufacturer = false;
$cigar_lines = false;
$details = false;
 
if(isset($_POST['manufacturer'])) 
  $manufacturer = (int)$_POST['manufacturer'];
if($manufacturer and isset($_POST['cigar_line'])) 
  $cigar_line = (int)$_POST['cigar_line'];
if($cigar_line and isset($_POST['detail'])) 
  $detail = (int)$_POST['detail'];
 
if(dropdown('manufacturer','id','manuf_name',$manufacturer,
            "select * from manufacturers order by manuf_name") and
  dropdown('cigar_line','line_id','line_name',$cigar_line,
           "select * from cigar_lines where id=$manufacturer order by line_name") and
  dropdown('detail','detail_id','details',$detail,
           "select * from details where line_id=$cigar_line order by details"))
  echo '<p>Selected: <br />'.
    'manufacturer='.$manufacturer.'<br />'.
    'cigar_line='.$cigar_line.'<br />'.
    'detail='.$detail.'</p>';
 
echo '</form>';
 
?>

Open in new window

Author

Commented:
Excellent answer. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial