PHP: Dynamic Dependent Select Box using jQuery and PHP ~ Tech Blog

Friday, 26 June 2015

PHP: Dynamic Dependent Select Box using jQuery and PHP

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.

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="http://cleartuts.blogspot.com/">cleartuts</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
 }
}
?>

Tech Blog

Friday, 26 June 2015