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.