How to Auto populate Dropdown with JavaScript AJAX and PHP

Dynamic dependent dropdown restricts the user selection based on the previous dropdown selection.

In this tutorial, I show how you can auto populate dropdown with MySQL database data using JavaScript and PHP.

How to Auto populate Dropdown with JavaScript AJAX and PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. PHP
  5. JavaScript
  6. Demo
  7. Conclusion

1. Table structure

I am using 3 tables in the example –

countries table (Store countries records) –

CREATE TABLE countries (
  id serial PRIMARY KEY,
  name varchar(80) NOT NULL
)

states table (Store states of the countries) –

CREATE TABLE states (
  id serial PRIMARY KEY,
  name varchar(80) NOT NULL,
  country_id bigint NOT NULL
)

cities table (Store cities of the states) –

CREATE TABLE cities (
  id serial PRIMARY KEY,
  name varchar(80) NOT NULL,
  state_id bigint NOT NULL
)

2. Configuration

Create a new config.php file.

Completed Code

<?php

$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = "root"; /* Password */
$dbname = "tutorial"; /* Database name */

// Create connection
$con = new mysqli($host, $user, $password, $dbname);

// Check connection
if ($con->connect_error) {
   die("Connection failed: " . $con->connect_error);
}

3. HTML

Fetch all records from countries table and create 3 <select> elements –

  • First <select > element is to display fetched countries.
  • Second is use to display states based on country selection using JavaScript AJAX, and
  • Third is use to display cities based on state selection using JavaScript AJAX.

Completed Code

<?php
include "config.php";

// Fetch countries
$sql = "SELECT * from countries order by name";
$stmt = $con->prepare($sql); 
$stmt->execute();
$result = $stmt->get_result();

?>
<table>
   <tr>
      <td>Country</td>
      <td>
         <select id="country" onchange="getStates(this.value);">
            <option value="0" >– Select Country –</option>
            <?php
            while ($row = $result->fetch_assoc() ){

               $id = $row['id'];
               $name = $row['name'];

               echo "<option value='".$id."' >".$name."</option>";
            }
            ?>
         </select>
      </td>
   </tr>

   <tr>
      <td>State</td>
      <td>
         <select id="state" onchange="getCities(this.value);" >
            <option value="0" >– Select State –</option>
         </select>
      </td>
   </tr>

   <tr>
      <td>City</td>
      <td>
         <select id="city" >
            <option value="0" >– Select City –</option>
         </select>
      </td>
   </tr>
</table>

4. PHP

Create ajaxfile.php file.

Handle 2 AJAX requests –

  • If $request == ‘getStates’ – Fetch records from states table according to $country_id value and assign to $result. Loop on $result and initialize $data Array with id and name keys.

Return $data in JSON format.

  • If $request == ‘getCities’ – Fetch records from cities table according to $state_id value and assign to $result. Loop on $result and initialize $data Array with id and name keys.

Return $data in JSON format.

Completed Code

<?php
include 'config.php';

// Read POST data
$postData = json_decode(file_get_contents("php://input"));
$request = "";
if(isset($postData->request)){
   $request = $postData->request;
}

// Get states
if($request == 'getStates'){
   $country_id = 0;
   $result = array();$data = array();

   if(isset($postData->country_id)){
       $country_id = $postData->country_id;

       $sql = "SELECT * from states WHERE country_id=?";
       $stmt = $con->prepare($sql); 
       $stmt->bind_param("i", $country_id);
       $stmt->execute();
       $result = $stmt->get_result();

       while ($row = $result->fetch_assoc()){

          $id = $row['id'];
          $name = $row['name'];

          $data[] = array(
             "id" => $id,
             "name" => $name
          );

       }

   }

   echo json_encode($data);
   die;

}

// Get cities
if($request == 'getCities'){
   $state_id = 0;
   $result = array();$data = array();

   if(isset($postData->state_id)){
      $state_id = $postData->state_id;

      $sql = "SELECT * from cities WHERE state_id=?";
      $stmt = $con->prepare($sql); 
      $stmt->bind_param("i", $state_id);
      $stmt->execute();
      $result = $stmt->get_result();

      while ($row = $result->fetch_assoc()){

         $id = $row['id'];
         $name = $row['name'];

         $data[] = array(
            "id" => $id,
            "name" => $name
         );

      }
   }

   echo json_encode($data);
   die;
}

5. Javascript

Create 2 functions –

  • getStates() – This function calls when selection is changed on country dropdown.

Empty state and city dropdown. Send AJAX POST request to ajaxfile.php, pass {request: 'getStates', country_id: country_id} as data, and set dataType: 'json'.

On successful callback loop on response and add <option > in state dropdown.

  • getCities() – This function calls when selection is changed on state dropdown.

Empty the #city dropdown and send AJAX POST request to ajaxfile.php, pass {request: 'getCities', state_id: state_id} as data, and set dataType: 'json'.

On successful callback loop on response and add <option > in city dropdown.

Completed Code

function getStates(country_id){

   // Empty the dropdown
   var stateel = document.getElementById('state');
   var cityel = document.getElementById('city');

   stateel.innerHTML = "";
   cityel.innerHTML = "";

   var stateopt = document.createElement('option');
   stateopt.value = 0;
   stateopt.innerHTML = '-- Select State --';
   stateel.appendChild(stateopt);

   var cityopt = document.createElement('option');
   cityopt.value = 0;
   cityopt.innerHTML = '-- Select City --';
   cityel.appendChild(cityopt);

   // AJAX request
   var xhttp = new XMLHttpRequest();
   xhttp.open("POST", "ajaxfile.php", true); 
   xhttp.setRequestHeader("Content-Type", "application/json");
   xhttp.onreadystatechange = function() {
      if (this.readyState == 4 && this.status == 200) {
         // Response
         var response = JSON.parse(this.responseText);

         var len = 0;
         if(response != null){
            len = response.length;
         }

         if(len > 0){
            // Read data and create <option >
            for(var i=0; i<len; i++){

               var id = response[i].id;
               var name = response[i].name;

               // Add option to state dropdown
               var opt = document.createElement('option');
               opt.value = id;
               opt.innerHTML = name;
               stateel.appendChild(opt);

            }
         }
      }
   };
   var data = {request:'getStates',country_id: country_id};
   xhttp.send(JSON.stringify(data));

}

function getCities(state_id){

   // Empty the dropdown
   var cityel = document.getElementById('city');

   cityel.innerHTML = "";

   var cityopt = document.createElement('option');
   cityopt.value = 0;
   cityopt.innerHTML = '-- Select City --';
   cityel.appendChild(cityopt);

   // AJAX request
   var xhttp = new XMLHttpRequest();
   xhttp.open("POST", "ajaxfile.php", true); 
   xhttp.setRequestHeader("Content-Type", "application/json");
   xhttp.onreadystatechange = function() {
      if (this.readyState == 4 && this.status == 200) {
         // Response
         var response = JSON.parse(this.responseText);

         var len = 0;
         if(response != null){
            len = response.length;
         }

         if(len > 0){
            // Read data and create <option >
            for(var i=0; i<len; i++){

               var id = response[i].id;
               var name = response[i].name;

               // Add option to city dropdown
               var opt = document.createElement('option');
               opt.value = id;
               opt.innerHTML = name;
               cityel.appendChild(opt);

            }
         }
      }
   };
   var data = {request:'getCities',state_id: state_id};
   xhttp.send(JSON.stringify(data));
}

6. Demo

View Demo


7. Conclusion

Follow the same steps to auto-populate multiple dropdowns.

If data is not populating on dropdown when you select a country or a state then use the browser network tab to debug. Check SQL queries and POST values again.

If you found this tutorial helpful then don't forget to share.

Leave a Comment