How to Auto populate dropdown with AJAX PDO and PHP

With Autopopulating dropdowns user is restricted to select an option from the dropdown steps by steps. Based on the dropdown selection next dropdown is autofill with options.

The common example, of this, is country, state, and city dropdowns on a form.

To load data without page refresh need to use jQuery AJAX.

In this tutorial, I show how you can auto-populate the dropdown with PDO and PHP.

How to auto populate dropdown with AJAX PDO and PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML & PHP
  4. jQuery
  5. AJAX File
  6. Demo
  7. Conclusion

1. Table structure

I am using 3 tables in the example –

countries table (Store countries) –

CREATE TABLE `countries` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

states table (Store states of the countries) –

CREATE TABLE `states` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `country` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

cities table (Store cities of the states) –

CREATE TABLE `cities` (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(80) NOT NULL,
  `state` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Configuration

Create a config.php for a database connection.

Completed Code

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";

// Create connection
try{
   $conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Unable to connect with the database');
}

3. HTML & PHP

Create 3 <select> elements.

Populate 1st <select > element with countries list and set empty the other 2 <select > elements.

The 2nd <select > is populated with state names based on a country selection from the 1st <select > element using jQuery AJAX.

Similarly, the 3rd <select > is populated with city names based on the state selection from the 2nd <select> element using jQuery AJAX.

Completed Code

<?php include "config.php"; ?>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>

<table>
  <tr>
    <td>Country</td>
    <td>
       <!-- Country dropdown -->
       <select id='sel_country' >
          <option value='0' >Select Country</option>
          <?php 
          ## Fetch countries
          $stmt = $conn->prepare("SELECT * FROM countries ORDER BY name");
          $stmt->execute();
          $countriesList = $stmt->fetchAll();

          foreach($countriesList as $country){
             echo "<option value='".$country['id']."'>".$country['name']."</option>";
          }
          ?>
       </select>
    </td>
  </tr>

  <tr>
    <td>State</td>
    <td>
      <select id='sel_state' >
         <option value='0' >Select State</option>
      </select>
    </td>
  </tr>

  <tr>
    <td>City</td>
    <td>
      <select id='sel_city' >
        <option value='0' >Select City</option>
      </select>
    </td>
  </tr>
</table>

4. jQuery

Define change event on the <select id='sel_country'> and <select id='sel_state'>.

Country selection –

  • Read the selected value. Empty the state and city dropdown and send AJAX request to 'ajaxfile.php' and pass { request: 1, countryid: countryid } as data.

On AJAX successful callback loop on the JSON response and append states list using <option > in <select id='sel_state'> element.

State selection –

  • Read the selected value. Empty the city dropdown and send AJAX request to 'ajaxfile.php' and pass { request: 2, stateid: stateid } as data.

On AJAX successful callback loop on the JSON response and append cities list using <option > in <select id='sel_city'> element.

Completed Code

$(document).ready(function(){

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

     var countryid = $(this).val();

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

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

         var len = response.length;

         for( var i = 0; i<len; i++){
           var id = response[i]['id'];
           var name = response[i]['name'];

           $("#sel_state").append("<option value='"+id+"'>"+name+"</option>");

         }
       }
     });

  });

  // State
  $('#sel_state').change(function(){
     var stateid = $(this).val();

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

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

         var len = response.length;

         for( var i = 0; i<len; i++){
           var id = response[i]['id'];
           var name = response[i]['name'];

           $("#sel_city").append("<option value='"+id+"'>"+name+"</option>");

         }
       }
     });
   });
});

5. AJAX File

Create an ajaxfile.php file.

Handle 2 requests –

  • If $request == 1 – Read the POST countryid. Fetch records from the states table based on the country id.

Loop on the fetched records and initialize $response Array with an associative array which has id and name keys.

Return $response Array in JSON format.

  • If $request == 2 – Read the POST stateid. Fetch records from the cities table based on the state id.

Loop on the fetched records and initialize $response Array with an associative array which has id and name keys.

Return $response Array in JSON format.

Completed Code

<?php

include "config.php";

$request = 0;

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

// Fetch state list by countryid
if($request == 1){
   $countryid = $_POST['countryid'];

   $stmt = $conn->prepare("SELECT * FROM states WHERE country=:country ORDER BY name");
   $stmt->bindValue(':country', (int)$countryid, PDO::PARAM_INT);

   $stmt->execute();
   $statesList = $stmt->fetchAll();

   $response = array();
   foreach($statesList as $state){
      $response[] = array(
        "id" => $state['id'],
        "name" => $state['name']
      );
   }

   echo json_encode($response);
   exit;
}

// Fetch city list by stateid
if($request == 2){
   $stateid = $_POST['stateid'];

   $stmt = $conn->prepare("SELECT * FROM cities WHERE state=:state ORDER BY name");
   $stmt->bindValue(':state', (int)$stateid, PDO::PARAM_INT);

   $stmt->execute();
   $statesList = $stmt->fetchAll();

   $response = array();
   foreach($statesList as $state){
      $response[] = array(
         "id" => $state['id'],
         "name" => $state['name']
      );
   }

   echo json_encode($response);
   exit;
}

6. Demo

View Demo


7. Conclusion

You need to bind change event on the dropdown on which selection bases you want to auto-populate another dropdown.

In the example, I auto-populate 2 dropdowns but you can similarly auto-populate single or more dropdowns if you have on a page.

If you found this tutorial helpful then don't forget to share.
Are you want to get implementation help, or modify or extend the functionality of this script? Submit paid service request.

9 thoughts on “How to Auto populate dropdown with AJAX PDO and PHP”

  1. Hi there, sir!
    I found your tutorial extremely helpful.
    But I’m trying to adapt it to my code which is in MVC, and I can’t get it to work.

    Would you mind make a slightly modified version where in the url: you call lets say a controller like: url: ‘Country/get_statesByCountryId’,
    Contry_Controller Code:
    function get_statesByCountryId()
    {
    $countryId = $_POST[‘countryId’]
    $response = $this->country_model->select_states_by_country($deptoId);
    echo json_encode($response);
    }

    And then in the Controller you call the Model to execute the select statement, like:
    Country_Model Code:
    function select_states_by_country($contryId = “”)
    {
    $criteria = array(‘contry_id’ => $contryId);
    $query = $this->db->get_where(‘states’, $criteria);
    $statesList = $query->result_array();
    $response = array();
    foreach ($statesList as $state) {
    $response[] = array(
    “id” => $state[‘id’],
    “name” => $state[‘name’]
    );
    }
    return $response;
    }

    I really dont know what coub be wrong with my code. I just can’t get the states dropdown to be populated.

    Thanks and regards.

    Reply
  2. Is it possible to use the result of this to post the data to the database ?

    I.e. set the customer country, state and city for a new customer ?

    If so… how would you go about achieving this?

    Reply
  3. I select the information, for example: I select “Blues”, and save the name Blues in the database instead of recording the character “1”.
    Help me correct this error.

    Eye color

    Choose
    <option eye-color == “1”){echo “selected”;}?>>Blues
    <option eye-color == “2”){echo “selected”;}?>>Brown
    <option eye-color == “3”){echo “selected”;}?>>Black
    <option eye-color == “4”){echo “selected”;}?>>Greens

    I’m using PDO :: FETCH_OBJ

    Reply
  4. Hi ist so trrible for me. I try it long time but wenn i fill my own datas in tables you made. It does not work.
    Online the country select runs

    Reply
  5. Hello sir and thank you for the code.

    I build a web app with students teachers and classrooms. When i want to edit a student how do i fetch back the values from the database on to the dependent dropdown menus so i can change anything i want?

    Reply

Leave a Comment