Tutorial : Dynamic Dependent Select Box using Jquery and PHP

How many times have you tried to search for the best method to dynamically link your select boxes. This tutorial post will explain you how to create a dynamic dependent select box using Jquery and PHP.

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. Take a look at this live demo.

STEP 1: Design Database and Tables

Step 1A :

Create Database locations using below mysql query or use simply phpmyadmin :

[code]CREATE DATABASE locations;[/code]

Step 1B:

Create Tables for countries, states, and cities. States table has a relation with countries table and cities table has a relation with states table.

Countries Table:

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

[code]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;[/code]

States Table:

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

[code]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;[/code]

Cities Table:

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

[code]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;[/code]

Step 2 : dbConfig.php File

This file helps to connect and select the database.

[code]<?php //db details $dbHost = ‘localhost’; $dbUsername = ‘root’; $dbPassword = ”; $dbName = ‘locations’; // write your database name here //Connect and select the database $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
?>[/code]

Create 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.

[code]<?php //Include database configuration file include(‘dbConfig.php’); 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>’;
}
}
?>[/code]

Using PHP & HTML the initial country select box is displayed along with the state and city select box.

[code]<?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>[/code]

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.

//Count total number of rows
$rowCount = $query->num_rows;

Finally our ‘Dynamic Dependent Select Box using Jquery and PHP’ is complete and ready to run.

Don’t forget to share your doubts in the comment box and also share this post on social media and with your friends because “You share, I share, let’s make the world aware”.

You may want to take a look at the following related posts:

Also for more awesome tutorials, please don’t forget to like our facebook page Meul Tech .

Bonus: We also give training on following topics:

1. Web Designing Training in Mumbai.

2. Bootstrap Training Course in Mumbai.

3. Python Training in Mumbai.

4. UI / UX Training.

5. IOS Training Institute in Mumbai.

Tags: jquery, PHP