How to bind Country State City Using PHP PDO

Stature for country table

CREATE TABLE `countries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `countryname` varchar(80) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

Stature for State table

CREATE TABLE `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stateame` varchar(80) NOT NULL,
  `countryname` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

Stature for City table

CREATE TABLE `cities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cityname` varchar(80) NOT NULL,
  `statename` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8;

config.php

<?php
$server = "localhost";
$username = "root";
$password = "";
$dbname = "tutorial";
try{
   $conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Unable to connect with the database');
}

index.php

<?php 
include "config.php";
?>
<!DOCTYPE html>
<html>
<head>
	<title>How To Bind Country State City using PHP PDO Ajax</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<body>
	<link href="https://netdna.bootstrapcdn.com/bootstrap/3.1.0/css/bootstrap.min.css" rel="stylesheet" id="bootstrap-css">
<div class="container">
        <div class="row centered-form">
        <div class="col-xs-12 col-sm-8 col-md-4 col-sm-offset-2 col-md-offset-4">
           <div class="panel panel-default">
              <div class="panel-heading">
                   <h3 class="panel-title">How To Country,State,City Bind Using PHP PDo,Ajax <small><a href="http://thephpconcept.com/">The PHP Concept</a></small></h3>
                   </div>
                   <div class="panel-body">
                   <form method="POST" action="insert.php">
                      
                        <div class="form-group">
                         <label for="qualification">Country</label>
                         <select name="qualification" id="sel_country" class="form-control input-sm" placeholder="Qualification" required="">
		          	<option value='0' >Select Country</option>
		          	<?php 
		          	## Fetch countries
					$stmt = $conn->prepare("SELECT * FROM countries ORDER BY countryname");
					$stmt->execute();
					$countriesList = $stmt->fetchAll();

					foreach($countriesList as $country){
						echo "<option value='".$country['id']."'>".$country['countryname']."</option>";
					}
		          	?>
		        	</select>
                      </div>

                      <div class="form-group">
                         <label for="name">State</label>
                          <select name="qualification" id="sel_state" class="form-control input-sm">
		          	<option value='0' >Select State</option>
		          </select>
                      </div>

                       <div class="form-group">
                         <label for="name">City</label>
                          <select name="qualification" id="sel_city" class="form-control input-sm">
		          	<option value='0' >Select City</option>
		          </select>
                      </div>
                      
                     
                   </form>
                </div>
             </div>
          </div>
       </div>
    </div>
    



	<!-- Script -->
	<script type="text/javascript">
	$(document).ready(function(){

		// Country
		$('#sel_country').change(function(){

			var countryid = $(this).val();
			
			// Empty state and city dropdown
			$('#sel_state').find('option').not(':first').remove();
			$('#sel_city').find('option').not(':first').remove();

			// AJAX request
			$.ajax({
				url: 'ajaxfile.php',
				type: 'post',
				data: {request: 1, countryid: countryid},
				dataType: 'json',
				success: function(response){
					
					var len = response.length;

		            for( var i = 0; i<len; i++){
		                var id = response[i]['id'];
		                var name = response[i]['stateame'];
		                    
		                $("#sel_state").append("<option value='"+id+"'>"+name+"</option>");

		            }
				}
			});
			
		});


		// State
		$('#sel_state').change(function(){
			var stateid = $(this).val();
			
			// Empty city dropdown
			$('#sel_city').find('option').not(':first').remove();

			// AJAX request
			$.ajax({
				url: 'ajaxfile.php',
				type: 'post',
				data: {request: 2, stateid: stateid},
				dataType: 'json',
				success: function(response){
					
					var len = response.length;

		            for( var i = 0; i<len; i++){
		                var id = response[i]['id'];
		                var cityname = response[i]['cityname'];
		                    
		                $("#sel_city").append("<option value='"+id+"'>"+cityname+"</option>");

		            }
				}
			});
		});
	});
	</script>
</body>
</html>

ajaxfile.php

<?php 

include "config.php";

$request = 0;

if(isset($_POST['request'])){
	$request = $_POST['request'];
}

// Fetch state list by countryid
if($request == 1){
	$countryid = $_POST['countryid'];

	$stmt = $conn->prepare("SELECT * FROM states WHERE countryname=:countryname ORDER BY stateame");
	$stmt->bindValue(':countryname', (int)$countryid, PDO::PARAM_INT);

	$stmt->execute();
	$statesList = $stmt->fetchAll();

	$response = array();
	foreach($statesList as $state){
		$response[] = array(
				"id" => $state['id'],
				"stateame" => $state['stateame']
			);
	}

	echo json_encode($response);
	exit;
}

// Fetch city list by stateid
if($request == 2){
	$stateid = $_POST['stateid'];

	$stmt = $conn->prepare("SELECT * FROM cities WHERE statename=:statename ORDER BY cityname");
	$stmt->bindValue(':statename', (int)$stateid, PDO::PARAM_INT);

	$stmt->execute();
	$statesList = $stmt->fetchAll();

	$response = array();
	foreach($statesList as $state){
		$response[] = array(
				"id" => $state['id'],
				"cityname" => $state['cityname']
			);
	}

	echo json_encode($response);
	exit;
}

How to bind Country State City Using PHP PDO, Ajax With Source Code

Size: 2KB

Version: 1.0

Download Now!582 Downloads