Dynamic dependent select box using php

15/04/2017   04:00:52 PM

Hello, in this tutorial I will explain how to check live username availability.The dynamic dependent select box is mostly used for country state city dropdown. In this tutorial, we’ll implement relational dropdown of country state city using jQuery, Ajax, PHP and MySQL.

Means state is related with country and city is related with the state. Based on changing of country & state, respective state & city will be fetched from the database without reloading the page using jQuery, Ajax, PHP and MySQL.

dynamic_depndent_selectbox_jquery_ajax_php

At first, the country dropdown would be displayed with all countries. When a country would be chosen, the respective states would be fetched from the MySQL database and appear in the states dropdown. Alike when a state would be chosen, the respective cities will be fetched from the MySQL database and appear in the cities dropdown.

1) Create table 

  •  Countries

This table contains all the countries data. Countries table SQL would like below.

CREATE TABLE IF NOT EXISTS `countries` (

`country_id` int(11) NOT NULL,

`country_name` varchar(30) CHARACTER SET utf8 NOT NULL,

`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

  • States

This table contains all the states data and country_id FOREIGN KEY. States table SQL would like below.

CREATE TABLE IF NOT EXISTS `states` (

`state_id` int(11) NOT NULL,

`state_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,

`country_id` int(11) NOT NULL,

`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

 

  • Cities

This table contains all the cities data and state_id FOREIGN KEY. Cities table SQL would like below.

CREATE TABLE IF NOT EXISTS `cities` (

`city_id` int(11) NOT NULL,

`city_name` varchar(30) COLLATE utf8_unicode_ci NOT NULL,

`state_id` int(11) NOT NULL,

`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '0:Blocked, 1:Active'

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 

2) dbConfig.php File

<?php

$dbHost = 'localhost';

$dbUsername = 'root';

$dbPassword = '';

$dbName = 'location_db';

//Connect and select the database

$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);

if ($db->connect_error) {

die("Connection failed: " . $db->connect_error);

}

?>

 

3) index.php File

This file contains a jQuery library, JavaScript, HTML and PHP code. The following JavaScript code is used for getting the state and city data from ajaxData.php file using ajax. Also, it displays the returned HTML of the ajaxData.php file to the respective select box.

<style type="text/css">

.select-boxes{width: 280px;text-align: center;}

select {

background-color: #F5F5F5;

border: 1px double #FB4314;

color: #55BB91;

font-family: Georgia;

font-weight: bold;

font-size: 14px;

height: 39px;

padding: 7px 8px;

width: 250px;

outline: none;

margin: 10px 0 10px 0;

}

select option{

font-family: Georgia;

font-size: 14px;

}

</style>

<script src="jquery.min.js"></script>

<script type="text/javascript">

$(document).ready(function(){

$('#country').on('change',function(){

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

if(countryID){

$.ajax({

type:'POST',

url:'ajaxData.php',

data:'country_id='+countryID,

success:function(html){

$('#state').html(html);

$('#city').html('<option value="">Select state first</option>');

}

});

}else{

$('#state').html('<option value="">Select country first</option>');

$('#city').html('<option value="">Select state first</option>');

}

});

$('#state').on('change',function(){

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

if(stateID){

$.ajax({

type:'POST',

url:'ajaxData.php',

data:'state_id='+stateID,

success:function(html){

$('#city').html(html);

}

});

}else{

$('#city').html('<option value="">Select state first</option>');

}

});

});

</script>

<div class="select-boxes">

<?php

//Include database configuration file

include('dbConfig.php');

//Get all country data

$query = $db->query("SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC");

//Count total number of rows

$rowCount = $query->num_rows;

?>

<select name="country" id="country">

<option value="">Select Country</option>

<?php

if($rowCount > 0){

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

echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>';

}

}else{

echo '<option value="">Country not available</option>';

}

?>

</select>

<select name="state" id="state">

<option value="">Select country first</option>

</select>

<select name="city" id="city">

<option value="">Select state first</option>

</select>

</div>

 

4) ajaxData.php File

This file is requested by the Ajax and in this file state or city data is fetched from the database based on the requested country_id or state_id. Also, the respective select options HTML are returned to the Ajax success function.

<?php

include('dbConfig.php'); //Include database configuration file

if(isset($_POST["country_id"]) && !empty($_POST["country_id"])){

//Get all state data

$query = $db->query("SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC");

//Count total number of rows

$rowCount = $query->num_rows;

//Display states list

if($rowCount > 0){

echo '<option value="">Select state</option>';

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

echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>';

}

}else{

echo '<option value="">State not available</option>';

}

}

if(isset($_POST["state_id"]) && !empty($_POST["state_id"])){

//Get all city data

$query = $db->query("SELECT * FROM cities WHERE state_id = ".$_POST['state_id']." AND status = 1 ORDER BY city_name ASC");

//Count total number of rows

$rowCount = $query->num_rows;

//Display cities list

if($rowCount > 0){

echo '<option value="">Select city</option>';

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

echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>';

}

}else{

echo '<option value="">City not available</option>';

}

}

?>


Output

                   

Support us by Sharing