Make the options of the 2nd select box populate depending on the 1st. Both are PHP arrays

Question

I have two innoDB tables in my database named customers and vessels. I also have a form with 2 select boxes one having the column: company_name of table: customers as options, and the other having the column: vessel_name of table: vessels.

What i want to do is make the options of the 2nd select box populate according to the customer's company_name chosen in the 1st select box.

Finally please take into consideration that i am a complete newbie in Javascript and jQuery and thats why i am asking here how can i achieve the above result.

The form:

The php to make the above form work :

    query($sqlpelates)) {

$pelates = array();

    while($pelate = $pelat->fetch_object()) {
        $pelates[] = $pelate;
    }

$pelat->free();

}

// For vessels
$sqlploia = "SELECT * FROM vessels ORDER BY vessel_name";

if($plo = $db->query($sqlploia)) {

$ploia = array();

    while($ploi = $plo->fetch_object()) {
        $ploia[] = $ploi;
    }

$plo->free();

}

?>

UPDATE: Below is the single .php page where i am trying to achieve the above result:

query($sqlpelates)) {
    $pelates = array();
    while($pelate=$pelat->fetch_object()) $pelates[] = $pelate;
    $pelat->free();
  }
}
// modify the query to filter out only what your ajax request wants
$where = $ajax ? ' WHERE company_name="'.$_POST['companyName'].'"' : '';
// you need to make sure to escape the incoming variable $_POST['company_name']
$sqlploia = 'SELECT * FROM vessels'.$where.' ORDER BY vessel_name';
if ($plo=$db->query($sqlploia)) {
  $ploia = array();
  while($ploi=$plo->fetch_object()) $ploia[] = $ploi;
  $plo->free();
}
// the secret sauce... and some very bad programming, this should be done some other way
if ($ajax) {
  // set the type, so the client knows what the server returns
  header('Content-Type: application/json');
  // output what the client asked for: an array of vessels in JSON format
  echo json_encode($ploia);
  // kill the script, this is all the client wants to know
  exit;
}



?>

FINAL-UPDATE :

test.php:

query("SELECT * FROM `customers`")) {
    while($cterm2 = $cterm->fetch_object()) {
        $cus[] = $cterm2;
    }
}

?>

test.js:

function load (thediv, thefile) {
    // body...
    if(window.XMLHttpRequest) {
        xmlhttp = new XMLHttpRequest();
    } else {
        xmlhttp = new ActiveXObject('Microsoft.XMLHTTP');
    }

    xmlhttp.onreadystatechange = function() {
        if (xmlhttp.readyState==4 && xmlhttp.status==200) {
            document.getElementById(thediv).innerHTML = xmlhttp.responseText;
        }
    }

    xmlhttp.open('GET', thefile+'?selection='+document.myform.selection.value, true);
    xmlhttp.send();


}

test2.php:

query(
        "SELECT `vessel_name` FROM `vessels` WHERE `company_name` = '$selection'")) {
        while ($vterm2 = $vterm->fetch_object()) {
            $ves[] = $vterm2;
        }

    } else {
    echo 'Please type a customer name.';
    }
?>

hanzo2001 · Accepted Answer

This is not the first time I see this asked but I will dive in

Warning: this answer has javascript, with jQuery. I will also append a php file afterwards with some changes to allow the same script to be called for the ajax request

// jQuery must be loaded already
$(function(){
  var
    // put the target php script
    url = 'http://localhost/test/stackoverflow.php',
    form=$('form[name="ypo"]'), company, vessels;
  company = {
    // I prefer using native DomElements sometimes
    selectBox : $(form).find('select[name="company_name"]')[0],
    onSelect : function () {
      var
        idx = company.selectBox.selectedIndex,
        data;
      // if user selected an empty option, clear and return
      if (idx === -1) {vessels.clearBox();return;}
      // setup the data 
      data = {"ajax":1,"company_name":company.selectBox[idx].value};
      // your script now has $_GET['ajax'], $_GET['company_name']
      $.post(url,data,vessels.fillBox,'json');
      // vessels.fillbox will be executed when your php script returns
    }
  };
  vessels = {
    // I prefer using native DomElements sometimes
    selectBox : $(form).find('select[name="vessel"]')[0],
    // a handy method for clearing options
    clearBox : function () {$(this.selectBox).empty()},
    // called upon completion of the ajax request
    fillBox : function (arrayOfVessels) {
      // clear current contents
      $(this.selectBox).empty();
      // for each element in the array append a new option to the vessel selector
      arrayOfVessels.forEach(function(v){
        $(vessels.selectBox).append('');
      });
    }
  };
  // add a listener to the company selector
  $(company.selectBox).change(company.onSelect);
});

The logic behind the js code is to allow user interaction. When the user makes a selection a request is fired to the server and the response is processed in the client and populates your 2nd company_name.'">'.$p->company_name.''; ?>

Ok, now some pointers

you should be carefull with the php script I left there, there are other ways of doing what I intended which are cleaner and easier to maintain
the javascript is not the best, there are better solutions out there so be sure to check those out as well
If you do not understand parts of any of the scripts don't hesitate to ask
Beware any whitespace, do not allow any output before the php script, this is very important. All output should be left to the template

I hope this has been helpfull

Make the options of the 2nd select box populate depending on the 1st. Both are PHP arrays

Answers (2)

Related Questions