Multiple Select Dropdown In PHP MySQL

By : PHPErrorCode August 1, 2017 3109 Views php,ajax

Hello Guys, Many times while creating a Form, you need to create a dropdown list in which selecting country in one dropdown list will gives state list in second drop down list and after selecting state in that dropdown list gives city list in third dropdown list. It is also useful in various web application For example, in e-Commerce selecting one category will gives list of subcategory to other dropdown list. So Today we are going to create such a type of relative Dropdown list step by step.

Step 1: Create a database

  1. Open phpmyadmin
  2. Create database as "tutorial"
  3. Then we are going to create 3 tables respectively for Country, State and City.

-- create table and name it as "country"

CREATE TABLE `country` (
  `id` int(11) NOT NULL,
  `country_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- create table and name it as "state"

CREATE TABLE `state` (
  `id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- create table and name it as "city"

CREATE TABLE `city` (
  `id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step 2:Connect a database using MySQL:for config.php

In this step we are going to connect the database with our project.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";

  // Create connection
  $conn = new mysqli($servername, $username, $password, $dbname);

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

Step 3:Create a index.php file and put the bellow code into it.

Now, we are going to create a page in which we will use three level Dropdown list.

<?php require_once('config.php'); ?>
<!DOCTYPE html>
<html lang="en">
<head>
  <title>phperrorcode.com</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script type="text/javascript" src="custom.js"></script>
</head>
<body>

<div class="container well" style="width: 50%">
  <h2 class="text-center">Country State City drop down list using ajax in php demo</h2>
  <form>
    <div class="input-group">
      <select class="form-control" id="country">
        <option value="">---Select Country---</option>
        <?php $result = $conn->query("SELECT * FROM country");
            while ($row = $result->fetch_assoc()){ ?>
              <option value="<?php echo $row['id']; ?>"><?php echo $row['country_name']; ?></option>
            <?php } ?>
      </select>
      <span class="input-group-addon">Country</span>
    </div>
    <br>
    <div class="input-group">
      <select class="form-control" id="state">
        <option value="">---Select State---</option>
      </select>
      <span class="input-group-addon">State</span>
    </div>
    <br>
    <div class="input-group">
      <select class="form-control" id="city">
        <option value="">---Select City---</option>
      </select>
      <span class="input-group-addon">City  </span>
    </div>
  </form>
</div>

</body>
</html>

Step 4:Create a new named custom.js

Now we have created a custom.js file. When we select a country name from drop down list, it will pull state data from the state table without refreshing page. After selecting state it will pull city data from city table that we have created in step 1.

$(document).ready(function(){
    $("#country ").change(function(){
        var country_id = $(this).val();
        $.ajax({
            type: "POST",
            url: "search_state.php",
            data: 'id='+country_id,
            dataType: "html",
            async: false,
            success: function(data){
                $('#state').html(data);
                $('#city').html('<option value="">---Select City---</option>');
            }
        });
    });
    $("#state ").change(function(){
        var state_id = $(this).val();
        $.ajax({
            type: "POST",
            url: "search_city.php",
            data: 'id='+state_id,
            dataType: "html",
            async: false,
            success: function(data){
                $('#city').html(data);
            }
        });
    });
});

Step 5: Create A New File search_state.php :

This file will search for states record related country that user has selected

<?php
require_once('config.php');
$id = $_POST['id'];

$result = $conn->query("SELECT * FROM state where country_id = '$id'");
	
    if($result->num_rows > 0)
    {
        echo '<option value="">---Select State---</option>';

	// Fetch the table data	
	while ($row = $result->fetch_assoc()) 
	{
	    echo '<option value="'.$row['id'].'">'.$row['state_name'].'</option>';
	}
    }
    else
    {
	echo '<option value="">---No State Found---</option>';
    }
?>

Step 6: Create A New File search_city.php :

This file will search for cities record related state that user has selected

<?php
require_once('config.php');
$id = $_POST['id'];

$result = $conn->query("SELECT * FROM city where state_id = '$id'");
	
    if($result->num_rows > 0)
    {
	echo '<option value="">---Select City---</option>';

	// Fetch the table data	
	while ($row = $result->fetch_assoc()) 
	{
	    echo '<option value="'.$row['id'].'">'.$row['city_name'].'</option>';
	}
    }
    else
    {
	echo '<option value="">---No City Found---</option>';
    }
?>

That's it! The multiple dropdown list demo is ready to use. You can also create such a types of dropdown list for your website. Just copy the code and paste in appropriate files. If you need any types of help related to the code or would like to suggest improvements, do leave a comment below.