In this story, we are going to make dynamic drop down for Country , State and City selection using jQuery, Ajax, PHP and MySQL.
STEP 01: Set up Database Tables
First of all we make three tables. First table is for Countries in Database: `Dropdown`. Second Table is for States , which has Country ID as Foreign key. Third Table is for Cities of Selected States which has State ID as Foreign key. SQL Queries for creating tables are as follows:
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Table structure for table `country`
CREATE TABLE `country` (
`country_id` int(11) NOT NULL,
`country_code` char(3) NOT NULL DEFAULT ‘’,
`country` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Dumping data for table `country`
INSERT INTO `country` (`country_id`, `country_code`, `country`) VALUES
(1, ‘AUS’, ‘AUSTRALIA’), (2, ‘CAN’, ‘CANADA’), (3, ‘GBR’, ‘GREAT BRITAIN’), (4, ‘IND’, ‘INDIA’), (5, ‘PAK’, ‘PAKISTAN’), (6, ‘USA’, ‘USA’);
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Table structure for table `state`
CREATE TABLE `state` (
`state_id` int(3) NOT NULL,
`state` char(20) NOT NULL DEFAULT ‘’,
`country_code` char(3) NOT NULL DEFAULT ‘’
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Dumping data for table `state`
INSERT INTO `state` (`state_id`, `state`, `country_code`) VALUES
(1, ‘Maharashtra’, ‘IND’), (2, ‘Delhi’, ‘IND’), (3, ‘West Bengali’, ‘IND’), (4, ‘Tamil Nadu’, ‘IND’), (5, ‘Andhra Pradesh’, ‘IND’), (6, ‘Gujarat’, ‘IND’), (7, ‘Karnataka’, ‘IND’), (8, ‘Uttar Pradesh’, ‘IND’), (9, ‘Maharashtra’, ‘IND’), (11, ‘Sindh’, ‘PAK’), (12, ‘Punjab’, ‘PAK’), (13, ‘KPK’, ‘PAK’), (14, ‘Balochistan’, ‘PAK’), (15, ‘New York’, ‘USA’), (16, ‘California’, ‘USA’), (17, ‘Illinois’, ‘USA’), (18, ‘Texas’, ‘USA’), (19, ‘Pennsylvania’, ‘USA’), (20, ‘Arizona’, ‘USA’), (21, ‘California’, ‘USA’), (24, ‘Michigan’, ‘USA’), (25, ‘Qu?bec’, ‘CAN’), (26, ‘Alberta’, ‘CAN’), (27, ‘Ontario’, ‘CAN’), (29, ‘Manitoba’, ‘CAN’), (33, ‘British Columbia’, ‘CAN’), (35, ‘England’, ‘GBR’), (37, ‘Scotland’, ‘GBR’), (44, ‘Wales’, ‘GBR’), (46, ‘Victoria’, ‘AUS’), (48, ‘West Australia’, ‘AUS’), (49, ‘South Australia’, ‘AUS’), (50, ‘Capital Region’, ‘AUS’), (51, ‘Queensland’, ‘AUS’);
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Table structure for table `city`
CREATE TABLE `city` (
`city_id` int(4) NOT NULL,
`state_id` int(3) NOT NULL DEFAULT 0,
`city` char(35) NOT NULL DEFAULT ‘’
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
— Dumping data for table `city`
INSERT INTO `city` (`city_id`, `state_id`, `city`) VALUES
(1, 1, ‘Mumbai (Bombay)’), (2, 1, ‘Nagpur’), (3, 2, ‘Delhi’), (4, 3, ‘Calcutta [Kolkata]’), (5, 4, ‘Chennai (Madras)’), (6, 5, ‘Hyderabad’), (7, 6, ‘Ahmedabad’), (8, 7, ‘Bangalore’), (9, 8, ‘Kanpur’), (10, 8, ‘Lucknow’), (11, 9, ‘Mumbai (Bombay)’), (12, 9, ‘Nagpur’), (15, 11, ‘Karachi’), (16, 12, ‘Lahore’), (17, 13, ‘Peshawar’), (18, 14, ‘Quetta’), (19, 15, ‘New York’), (20, 16, ‘Los Angeles’), (21, 16, ‘San Diego’), (22, 17, ‘Chicago’), (23, 18, ‘Houston’), (24, 18, ‘Dallas’), (25, 18, ‘San Antonio’), (26, 19, ‘Philadelphia’), (27, 20, ‘Phoenix’), (28, 21, ‘Los Angeles’), (29, 21, ‘San Diego’), (36, 24, ‘Detroit’), (37, 25, ‘Montr?al’), (38, 26, ‘Calgary’), (39, 26, ‘Edmonton’), (40, 27, ‘Toronto’), (41, 27, ‘North York’), (42, 27, ‘Mississauga’), (43, 27, ‘Scarborough’), (44, 27, ‘Etobicoke’), (50, 29, ‘Winnipeg’), (63, 33, ‘Vancouver’), (69, 35, ‘London’), (70, 35, ‘Birmingham’), (71, 35, ‘Liverpool’), (72, 35, ‘Sheffield’), (73, 35, ‘Manchester’), (74, 35, ‘Leeds’), (75, 35, ‘Bristol’), (83, 37, ‘Glasgow’), (84, 37, ‘Edinburgh’), (122, 44, ‘Cardiff’), (127, 46, ‘Melbourne’), (130, 48, ‘Perth’), (131, 49, ‘Adelaide’), (132, 50, ‘Canberra’), (133, 51, ‘Brisbane’), (134, 51, ‘Gold Coast’);
— — — — — — — — — — — — — — — — — — — — — — — — — — — — —
— Indexes for table `city`
ALTER TABLE `city` ADD PRIMARY KEY (`city_id`);
— Indexes for table `country`
ALTER TABLE `country` ADD PRIMARY KEY (`country_id`), ADD UNIQUE KEY `country_code` (`country_code`);
— Indexes for table `state`
ALTER TABLE `state` ADD PRIMARY KEY (`state_id`);
— AUTO_INCREMENT for table `city`
ALTER TABLE `city` MODIFY `city_id` int(4) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=147;
— AUTO_INCREMENT for table `country`
ALTER TABLE `country` MODIFY `country_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
— AUTO_INCREMENT for table `state`
ALTER TABLE `state` MODIFY `state_id` int(3) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=55;
COMMIT;
STEP 02: Set up Database Connection
Create ‘config.php’ file in your Project Directory and paste following code to create Database Connection for your Drop down tables. Don’t forget to edit the Database, Host and User Names or Password as per your requirements.
<?Php
$host_name = “localhost”;
$database = “dropdown”; // Change your database nae
$username = “root”; // Your database user id
$password = “”; // Your password
try {
$dbo = new PDO(‘mysql:host=’.$host_name.’;dbname=’.$database, $username, $password);
} catch (PDOException $e) {
print “Error!: “ . $e->getMessage() . “<br/>”;
die(); }
?>
STEP 03: Create View / Index File
Now, Let’s create basic front-end of our Drop-down list. Create ‘index.php’ file in your Project Directory Containing following code.
<!DOCTYPE html>
<html> <head>
<meta name=”viewport” content=”width=device-width, initial-scale=1">
</head> <body>
<select name=”Country” id=”Country”>
<?php
include “config.php”;
$query= “SELECT * from country”;
$stmt=$dbo->prepare($query);
$stmt->execute();
$rows=$stmt->fetchALL();
foreach($rows as $country){
echo “<option value=’”.$country[‘country_id’].”’>”.$country[‘country’].”</option>”;
} ?>
</select>
<select name=”State” id=”state”></select>
<select name=”City” id=”city”></select>
<script src=”https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script type=”text/javascript” src=”ajax.js”></script> </body> </html>
STEP 04: Create ‘ajax.js’ File -(jQuery Function)
Create ‘ajax.js’ file in your directory and paste following Function Code. In below Code First function fetches states on change of country selection whereas second function fetches cities whenever state is changed.
$(“#Country”).change(function(){
var country = $(‘#Country’).find(“:selected”).val();
alert(country);
$.ajax({
url: “backend-script.php”,
type: “POST”, //Post method
data: {id : country}, //id for data fetching in backend-script
success: function(html){
$(“#state”).html(html);
alert(html);
}
});
});
// # is used for selecting id’s
$(“#state”).change(function(){
var state = $(‘#state’).find(“:selected”).val();
alert(state);
$.ajax({
url: “backend-script.php”,
type: “POST”, //Post method
data: {id1 : state}, //id for data fetching in backend-script
success: function(html){
$(“#city”).html(html);
alert(html);
}
});
});
STEP 04: Create Back-end Script to fetch States or Cities
Create a file “backend-script.php” in your project directory and paste following code. This Code fetches States or Cities data according to selected IDs.
<?php
include(“config.php”);
if(isset($_POST[“id”])){
$var1=$_POST[“id”]; //For getting data from ajax
$query1=”SELECT * from state1, country WHERE state1.country_id=country.country_id and country.country_id=$var1";
$stmt1=$dbo->prepare($query1);
$stmt1->execute();
$rows=$stmt1->fetchALL();
foreach($rows as $state1){
echo “<option value=’”.$state1[‘state_id’].”’>”.$state1[‘state’].”</option>”;
} }
if(isset($_POST[“id1”])){
$var2=$_POST[“id1”];
$query2=”SELECT * from state1, city WHERE city.state_id=state1.state_id and state1.state_id=$var2";
$stmt2=$dbo->prepare($query2);
$stmt2->execute();
$rows=$stmt2->fetchALL();
foreach($rows as $city){
echo “<option value=’”.$city[‘city_id’].”’>”.$city[‘city’].”</option>”;
} }
?>
“You can, you should, and if you’re brave enough to start, you will.”
No comments:
Post a Comment