Create Dynamic Dependent Dropdown with PostgreSQL PHP and AJAX

By auto-populating dropdown you can restrict users selection based on the parent dropdown selection.

Data is changed on child dropdowns every time selection is changed.

In this tutorial, I show how you can create dynamic dependent dropdown with PostgreSQL data using jQuery AJAX and PHP.

---

Contents

1. Table structure
2. Configuration
3. HTML
4. PHP
5. jQuery
6. Output
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";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");

if (!$con) {
   die('Connection failed.');
}

---

3. HTML

Fetch 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 jQuery AJAX, and
• Third is use to display cities based on state selection using jQuery AJAX.

Completed Code

<?php
include "config.php";

$sql = "select * from countries order by name";
$result = pg_query($con, $sql);
?>
<table>
   <tr>
      <td>Country</td>
      <td>
         <select id="country">
            <option value="0" >– Select Country –</option>
            <?php
            while ($row = pg_fetch_assoc($result) ){

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

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

   <tr>
      <td>State</td>
      <td>
         <select id="state" >
            <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';

$request = "";
if(isset($_POST['request'])){
   $request = $_POST['request'];
}

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

   if(isset($_POST['country_id'])){
      $country_id = $_POST['country_id'];

      $sql = "select * from states where country_id=$1";
      $result = pg_query_params($con, $sql, array($country_id));

      while ($row = pg_fetch_assoc($result) ){

         $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($_POST['state_id'])){
      $state_id = $_POST['state_id'];

      $sql = "select * from cities where state_id=$1";
      $result = pg_query_params($con, $sql, array($state_id));

      while ($row = pg_fetch_assoc($result) ){

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

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

      }
   }

   echo json_encode($data);
   die;
}

---

5. jQuery

Define change event on #country and #state.

• country – If a country is selected then empty the #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.

• state – If a state is selected then 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.

Completed Code

$(document).ready(function(){

   // Country
   $('#country').change(function(){

      // Country id
      var country_id = $(this).val();

      // Empty the dropdown
      $('#state').find('option').not(':first').remove();
      $('#city').find('option').not(':first').remove();

      // AJAX request
      $.ajax({
         url: 'ajaxfile.php',
         type: 'post',
         data: {request: 'getStates', country_id: country_id},
         dataType: 'json',
         success: function(response){

            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;

                  var option = "<option value='"+id+"'>"+name+"</option>";

                  $("#state").append(option);
               }
            }
         }
      });
   });

   // Country
   $('#state').change(function(){

      // State id
      var state_id = $(this).val();

      // Empty the dropdown
      $('#city').find('option').not(':first').remove();

      // AJAX request
      $.ajax({
         url: 'ajaxfile.php',
         type: 'post',
         data: {request: 'getCities', state_id: state_id},
         dataType: 'json',
         success: function(response){

            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;

                  var option = "<option value='"+id+"'>"+name+"</option>";

                  $("#city").append(option);
               }
            }

         }
      });
   });

});

---

6. Output

View Output

---

7. Conclusion

In the example, I am auto-populating two dropdowns but you can follow the same steps to add it on more dropdowns.

If data is not loading in the dropdown then use the browser network tab to debug.

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