This tutorial will cover creating a simple and Dynamic Dependent Select Box using jQuery and PHP for selecting state and city based on choosing the country, means Loading records from database dynamically and display it in select box without refreshing the whole page with the help of Ajax and jQuery and PHP code, Ajax is used to submit and get records from MySQL Database without page refresh. when you choose country box it will allows state box to choose country based state and same with city using Ajax code integrating with PHP and MySQL.
state table has relation with country table and city table has relation with state table.
tbl_country :
stores country name and country id
tbl_state :
stores state name and state id based on country id foreign key
tbl_city :
stores city name and city id with state id foreign key
That's it
download the script by clicking following link and try it. Feel free to comment your suggestions regarding this tutorial.
Database Design & Tables
create database "dbcountries" this database consist three tables country, state and city as below.state table has relation with country table and city table has relation with state table.
tbl_country :
stores country name and country id
CREATE TABLE `tbl_country` (
`country_id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`country_name` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM ;
tbl_state :
stores state name and state id based on country id foreign key
CREATE TABLE `tbl_state` (
`state_id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`country_id` INT( 3 ) NOT NULL ,
`state_name` VARCHAR( 35 ) NOT NULL
) ENGINE = MYISAM ;
tbl_city :
stores city name and city id with state id foreign key
CREATE TABLE `tbl_city` (
`city_id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`state_id` INT( 3 ) NOT NULL ,
`city_name` VARCHAR( 35 ) NOT NULL
) ENGINE = MYISAM ;
dbconfig.php
common database configuration file.
<?php
$DB_host = "localhost";
$DB_user = "root";
$DB_pass = "";
$DB_name = "dbcountries";
try
{
$DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e)
{
$e->getMessage();
}
index.php
contains jQuery/JavaScript and PHP code which displays three select box , country drop down box already filled with countries and state , city will be automatically change based on parent selection.
<?php
include_once 'dbconfig.php';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Dynamic Dependent Select Box using jQuery and PHP</title>
<script type="text/javascript" src="jquery-1.4.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function()
{
$(".country").change(function()
{
var id=$(this).val();
var dataString = 'id='+ id;
$.ajax
({
type: "POST",
url: "get_state.php",
data: dataString,
cache: false,
success: function(html)
{
$(".state").html(html);
}
});
});
$(".state").change(function()
{
var id=$(this).val();
var dataString = 'id='+ id;
$.ajax
({
type: "POST",
url: "get_city.php",
data: dataString,
cache: false,
success: function(html)
{
$(".city").html(html);
}
});
});
});
</script>
<style>
label
{
font-weight:bold;
padding:10px;
}
div
{
margin-top:100px;
}
select
{
width:200px;
height:35px;
}
</style>
</head>
<body>
<center>
<div>
<label>Country :</label>
<select name="country" class="country">
<option selected="selected">--Select Country--</option>
<?php
$stmt = $DB_con->prepare("SELECT * FROM tbl_country");
$stmt->execute();
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<option value="<?php echo $row['country_id']; ?>"><?php echo $row['country_name']; ?></option>
<?php
}
?>
</select>
<label>State :</label> <select name="state" class="state">
<option selected="selected">--Select State--</option>
</select>
<label>City :</label> <select name="city" class="city">
<option selected="selected">--Select City--</option>
</select>
</div>
<br />
<a href="https://codingcage.com/">Coding Cage</a>
</center>
</body>
</html>
get_state.php
contains PHP code which selects state records from state table and load data dynamically when selection made on country box.
<?php
include('dbconfig.php');
if($_POST['id'])
{
$id=$_POST['id'];
$stmt = $DB_con->prepare("SELECT * FROM tbl_state WHERE country_id=:id");
$stmt->execute(array(':id' => $id));
?><option selected="selected">Select State :</option><?php
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<option value="<?php echo $row['state_id']; ?>"><?php echo $row['state_name']; ?></option>
<?php
}
}
?>
get_city.php
contains PHP code which selects city records from city table and load data dynamically when selection made on state box.
<?php
include('dbconfig.php');
if($_POST['id'])
{
$id=$_POST['id'];
$stmt = $DB_con->prepare("SELECT * FROM tbl_city WHERE state_id=:id");
$stmt->execute(array(':id' => $id));
?><option selected="selected">Select City :</option><?php
while($row=$stmt->fetch(PDO::FETCH_ASSOC))
{
?>
<option value="<?php echo $row['city_id']; ?>"><?php echo $row['city_name']; ?></option>
<?php
}
}
?>
That's it
download the script by clicking following link and try it. Feel free to comment your suggestions regarding this tutorial.
nice share thank you bro
ReplyDeletePerfect Code! Thanks!
ReplyDeleteGreat beginning php tutorials Very clear and helpful for beginners.
ReplyDeletenice tutorial ...but can you make a tutorial on olx like "category selection" just click on the parent category , all the subsequent child category will opened and same like further click on child category ,,,all sub sequent sub-child category will opened ...Please update ..:-)..Thanks for your all tutorials it's superb ....
ReplyDeleteThanks very nice tutorial and web site!. :)
ReplyDeleteThis is very nice tutorial, i also written the same tutorial, hope you like it also. :) https://htmlcssphptutorial.wordpress.com/2015/08/25/dynamic-dependent-select-box-using-jquery-and-ajax/
ReplyDeleteLove u bro...!!! For Your nice tut
ReplyDeleteWell written tutorial, very well organized content and carefully picked topics! Congratulations and,of course, thank you !
ReplyDeleteyou're welcome :)
Deletehello! how i cant to save this options to another data table??
ReplyDeletehi there,
Deleteplease clear this question, so that i can help you .
I have this working great and really appreciate your help and tut. Now I would like this 3 drop down set to be one of 5 sets on the page that are all displaying the same database info. This is being used for getting calibration equipment quotes. So they can choose one item, the drop downs I have made from your tut, or up to 5 pieces. What do I need to change to duplicate the other 4 sets? Thanks again for your time and help.
ReplyDeleteas i done with three select box, try to do the same with 5 sets, create new pages as you want like this create and make some necessary changes in ajax code.
DeleteWhat about the last option having the same result for multiple options on the first and second selection.
ReplyDeleteCase 1:
Select box 1: Boy
Select box 2: Color
Select box 3: White
---
Case 2:
Select box 1: Girl
Select box 2: Color
Select box 3: White
Instead of having several white options, how can i have it all in one and letting white be an option for both cases?
thanks for this script ,it is possible to make a submit button and save these values to database .
ReplyDeleteHi, Please how can I get the Selected Text instead of the ID in the Value?? Please bro its urgent.
ReplyDeleteThanks very much for the clear code!!!
hi,how did you gotten the result to store the name instead of the ID?
DeleteI have setup everything, but its ID of the Country, State and City that is been stored in the Database. Please, how can I make the Name to store rather? Urgent bro ;)
ReplyDeletei can not download the script
ReplyDeleteplease help me
download it from the given link : Download
DeleteI am not able to download script either. Would be nice. This looks like what I need. Thanks for the tuturial
ReplyDeleteHello Shannon, sorry for the inconvenience, i have updated the download link get it from the same download link : download here
Deletehelpful tut,i would like to get the names(countrt,state and city) as value of the option instead of th ID
ReplyDeletethanks in advance
Very nice tutorial. Was very helpful. Thanks. Trying to insert the data from the dropdown to a table. When trying to insert, I am able to insert only the ids, not the values. Can you help me how to do this? Thanks in advance.
ReplyDeleteVery nice tutorial. Was of great help. Trying to insert the value from the dropdownlist into a table but instead able to insert only the id of the value. Can you help me insert the value?
ReplyDeleteGreat! exactely what i was looking for! Thank you very much.
ReplyDeleteExactely what i was looking for! Thank you very much
ReplyDeleteThanks very much, but please how can we insert the Country, State, and City Name into the databases instead of the ID?
ReplyDeleteThank for nice tutorial... we added to our cms user registration
ReplyDeletewhat about when click on edit user data state and city are not retrieved according country, please help me...
Đơn vị chuyên nhận order đặt mua hàng phụ kiện thời trang
ReplyDeleteCông ty chuyên nhận order đặt mua hàng thời trang ở Quảng Châu
Công ty chuyên nhận order đặt mua hàng quần áo ở Quảng Châu
Hi pradeep..Nice tutorial. If i need to filter a table based on two values(i should dynamically populate the third select box using the values from first and second select box), what should i do? In the above tut, we have only one value from each select values.
ReplyDeleteHi pradeep..Nice tutorial. If i need to filter a table based on two values(i should dynamically populate the third select box using the values from first and second select box), what should i do? In the above tut, we have only one value from each select values.
ReplyDeleteThank you! I just applied this to my site and it worked perfectly :D
ReplyDeletethanks a lot... but took a lot of time to include my project -_-
ReplyDelete