Multiple Select Dropdown List with AJAX

Ajax Course

There is a new version of this script, on this page: MSLA2 - Filter Content with Multiple Select Lists with Ajax .
- The content can be displayed to each Select list, and can be added from multiple columns, in the same MySQL table.
- That script can be included in ".php" and ".html" pages, so, it can be included in the template of a CMS.

This tutorial presents an AJAX script that can be used to create dynamically multiple Select lists with data from a MySQL table.

First, a single select list is displayed on the page, then when the user chooses an option, it calls an Ajax function that acceses a PHP file that will return a select list according to the option selected. The Ajax function receives and displays the second select list.
When the users selects an option from the second select list, this script can display a third list of options, according to the options selected in the first an second select lists.

The script contains two files, a PHP file (named "select_list.php") with the code that selects data from a MySQL table and returns a select drop down list, and a JS file (named "ajax_select.js") with the Ajax code that accesses the PHP file.

• You can copy and use the code for these files displayed below, or download the files from: Script Multiple Select Dropdown List

On this page: Multiple Drop-Down Select Lists Creator it is another Ajax script, with Administration Page, that can be used to create multiple select lists.

The code for select_list.php

<?php
// Multiple select lists - https://coursesweb.net/ajax/

// Here add your own data for connecting to MySQL database
$server = 'localhost';
$user = 'root';
$pass = 'pasword';
$dbase = 'dbname';

// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'links' if you don`t want to display their data too
$table = 'sites';
$ar_cols = array('site', 'menu', 'categ', 'links');

if(!isset($_SESSION)) session_start();
header('Content-type: text/html; charset=utf-8');

$preid = 'slo_';        // a prefix used for element's ID, in which Ajax will add <select>
$col = $ar_cols[0];     // the variable used for the column that wil be selected
$re_html = '';          // will store the returned html code

// if there is data sent via POST, with index 'col' and 'wval'
if(isset($_POST['col']) && isset($_POST['wval'])) {
  // set the $col that will be selected and the value for WHERE (delete tags and external spaces in $_POST)
  $col = trim(strip_tags($_POST['col']));
  $wval = trim(strip_tags($_POST['wval']));
}

$key = array_search($col, $ar_cols);  // get the key associated with the value of $col in $ar_cols
$wcol = $key===0 ? $col : $ar_cols[$key-1];  // gets the column for the WHERE clause
  
// gets the next element in $ar_cols (needed in the onchange() function in <select> tag)
$last_key = count($ar_cols)-1;
$next_col = $key<$last_key ? $ar_cols[$key+1] : '';

$conn = new mysqli($server, $user, $pass, $dbase);     // connect to the MySQL database
if (mysqli_connect_errno()) { exit('Connect failed: '. mysqli_connect_error()); }     // check connection

$_SESSION['ar_cols'][$wcol] = isset($wval) ? $conn->real_escape_string($wval) : $wcol;    // store in SESSION the column and its value for WHERE

// sets an array with data of the WHERE condition (column=value) for SELECT query
for($i=1; $i<=$key; $i++) {
  $ar_where[] = '`'. $ar_cols[$i-1]. "`='". $_SESSION['ar_cols'][$ar_cols[$i-1]] ."'";
}

// define a string with the WHERE condition, and then the SELECT query
$where = isset($ar_where) ? ' WHERE '. implode($ar_where, ' AND ') : '';
$sql = "SELECT DISTINCT `$col` FROM `$table`".$where;

$result = $conn->query($sql);   // perform the query and store the result

// if the $result contains at least one row
if ($result->num_rows > 0) {
  // sets the "onchange" event, which is added in <select> tag
  $onchg = $next_col!==null ? " onchange=\"ajaxReq('$next_col', this.value);\"" : '';

  // sets the select tag list (and the first <option>), if it's not the last column
  if($col!=$ar_cols[$last_key]) $re_html = $col. ': <select name="'. $col. '"'. $onchg. '><option>- - -</option>';

  while($row = $result->fetch_assoc()) {
    // if its the last column, reurns its data, else, adds data in OPTION tags
    if($col==$ar_cols[$last_key]) $re_html .= '<br/>'. $row[$col];
    else $re_html .= '<option value="'. $row[$col]. '">'. $row[$col]. '</option>';
  }

  if($col!=$ar_cols[$last_key]) $re_html .= '</select> '; // ends the Select list
}
else { $re_html = '0 results'; }

$conn->close();

// if the selected column, $col, is the first column in $ar_cols
if($col==$ar_cols[0]) {
  // adds html code with SPAN (or DIV for last item) where Ajax will add the select dropdown lists
  // with ID in each SPAN, according to the columns added in $ar_cols
  for($i=1; $i<count($ar_cols); $i++) {
    if($ar_cols[$i]===null) continue;
    if($i==$last_key) $re_html .= '<div id="'. $preid.$ar_cols[$i]. '"> </div>';
    else $re_html .= '<span id="'. $preid.$ar_cols[$i]. '"> </span>';
  }

  // adds the columns in JS (used in removeLists() to remove the next displayed lists when makes other selects)
  $re_html .= '<script type="text/javascript">var ar_cols = '.json_encode($ar_cols).'; var preid = "'. $preid. '";</script>';
}
else echo $re_html;

The code for ajax_select.js

// Multiple select lists - https://coursesweb.net/ajax/

// function used to remove the next lists already displayed when it chooses other options
function removeLists(colid) {
  var z = 0;
  // removes data in elements with the id stored in the "ar_cols" variable
  // starting with the element with the id value passed in colid
  for(var i=1; i<ar_cols.length; i++) {
    if(ar_cols[i]==null) continue;
    if(ar_cols[i]==colid) z = 1;
    if(z==1) document.getElementById(preid+ar_cols[i]).innerHTML = '';
  }
}

// sends data to a php file, via POST, and displays the received answer
function ajaxReq(col, wval) {
  removeLists(col);           // removes the already next selects displayed

  // if the value of wval is not '- - -' and '' (the first option)
  if(wval!='- - -' && wval!='') {
    var request = (window.XMLHttpRequest) ? new XMLHttpRequest() : new ActiveXObject('Microsoft.XMLHTTP');  // XMLHttpRequest instance
    var php_file = 'select_list.php';     // path and name of the php file

    // create pairs index=value with data that must be sent to server
    var  data_send = 'col='+col+'&wval='+wval;

    request.open("POST", php_file, true);			// set the request

    document.getElementById(preid+col).innerHTML = 'Loadding...';   // display a loading notification

    // adds a header to tell the PHP script to recognize the data as is sent via POST
    request.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
    request.send(data_send);	      	// calls the send() method with data_send

    // Check request status
    // If the response is received completely, will be added into the tag with id value of "col"
    request.onreadystatechange = function() {
      if (request.readyState==4) {
        document.getElementById(preid+col).innerHTML = request.responseText;
      }
    }
  }
}

Usage Script Multiple Select Drop down List

1) Create the files: "select_list.php" and "ajax_select.js" on your server (with the code presented above), in the same directory.
In the PHP script, add your own data for connecting to MySQL, the table name (in $table) and the name of the columns with data for each Select list (in the array $ar_cols).

- For example, if you have a table named "cities", and you want to create a triple select drop down list with data stored in the columns: "state", "city", and "villages"; then to display some descriptions about each village stored in another column named "villdata", change the value of the $table and $ar_cols variables like this:

// Here add the name of the table and columns that will be used for select lists, in their order
$table = 'cities';
$ar_cols = array('state', 'city', 'villages', 'villdata');

If you want to display only the three select lists (state, city, villages), or you not have a column with description, add null for the last element (in place of 'villdata').

- If you want to create two select drop down list, and then a description for the options of the second Select, add like this:

// Here add the name of the table and columns that will be used for select lists, in their order
$table = 'table_name';
$ar_cols = array('col_select1', 'col_select2', 'col_description');

If you want to display only the Select lists associated with "col_select1" and "col_select2", replace 'col_description' with null.

2) In the web page in which you want to display the select lists with Ajax:
- You must include the "select_list.php" file at the beginning of the page, by adding the following php code, before the HTML code, or any output data (this because it uses session_start()):

<?php include 'select_list.php'; ?>

- Include the "ajax_select.js" file in the HEAD section of the HTML code, by adding the following code:

<script src="ajax_select.js" type="text/javascript"></script>

- Then, in the form where you want to display the Select lists, add:

Select: <?php echo $re_html; ?>

Like you can see in the example below, or in the "test.php" file (in the archive downloaded from the link above).

• The PHP script creates the first <select> with the options from the first column added in $ar_cols Array, then creates HTML tags in which Ajax will add the next Select lists. These HTML tags has an unique ID composed by the string added in $preid variable and the name of the column. The $preid is used to be sure that there isn't another element with the same ID on the page.

If you want to include the select drop down lists into a form, and then to send and use the selected options in other PHP script, you can get the selected value using the "name" of each select, which is the same with the column name with the options for that select, added in the $ar_cols variable.
- For example, the first <select> has name="col_select1" (the first value added in the $ar_cols array). To get the selected option of this dropdown list, use in your PHP script: $_POST['col_select1'].
The second Select list has name="col_select2" (the second value added in the $ar_cols array), and so on.

- If you modify something in the instrutions of the PHP script or in the JavaScript functions, without knowing what you do, it's pretty sure the script to not work.

Example Triple Select drop down list

Here's is a concrete example. We have in MySQL a table named "sites" with these columns: id, site, menu, categ, links; having the following records:
We create a triple select list with options stored in the columns: "site", "menu", and "categ", then we'll display the data stored in the column "links", associated with the option selected in the last list (categ).
In the "select_list.php" we set the code:

// Here add the name of the table and columns that will be used for select lists, in their order
// Add null for 'links' if you don't want to display their data too
$table = 'sites';
$ar_cols = array('site', 'menu', 'categ', 'links');

The web page that will display the Select lists has the following code:

<?php include 'select_list.php'; ?>
<!doctype html>
<html>
<head>
<meta charset="utf-8" />
<title>Triple Select drop down list with Ajax</title>
<script src="ajax_select.js" type="text/javascript"></script>
</head>
<body>

<form action="" method="post">
Select: <?php echo $re_html; ?>
</form>

</body>
</html>

- Here's the result:

Example Double Select Drop down list

Now we create only two select dropdown list, with the options stored in the columns "site", and "menu" (in the same table presented above).
We just need to modify the $ar_cols variable (in "select_list.php"), by adding the elements 'site', 'menu', and null :

// Here add the name of the table and columns that will be used for select lists, in their order
$table = 'sites';
$ar_cols = array('site', 'menu', null);

- In the same web page created in the previous example, will result:

<form action="script.php" method="post"> ... </form>

#id {
  background-color: #bbfeda;
  box-shadow: 11px 11px 5px #7878da;
}

var fruits = ["apple", "apricot", "banana"];
fruits.shift();
alert(fruits.length);           // 2

if(extension_loaded("PDO") === true) echo "PDO is available."