Categories
makitweb.com

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,

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 type="text/javascript" src='https://ajax.googleapis.com/ajax/libs/jquery/3.4.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 a 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

Select a country.


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.

Leave a Reply

Your email address will not be published. Required fields are marked *