Posted on by

Ajax dropdown list from Database using PHP and jQuery

Dropdown is the best way to force user to select any one option by giving him/her a list of options. But sometimes there is a dependency to select next option which is based on previous option.

Just like, if you are in a shopping cart website trying to purchase any product. First you must select a country then website automatically populates a list of cities based on your selection.

This whole process from country to cities is based on AJAX. First shopping cart website shows country in a dropdown from database and when user select any country then cities are populated under countries dropdown.

So in this tutorial we are going to cover this process.


Ajax dropdown list from database using PHP and jQuery

Let’s note down what we are going to do.

  • Create database as demo.
  • Create 2 tables in the demo database. One table is for country and second is for cities.
  • Create a config.php file and create database connection using mysqli_connect() function.
  • Create index.php file which include config.php file, write css, html, php and jquery ajax code.
  • Create ajax_request.php which will get country id and populate cities based on given country id.

ajax country cities dropdown

Create Database:

1
2
3
 
Create database demo;
 

Create Country Table:

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE `country` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`country_name` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=13
;
 

Insert records in Country Table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
INSERT INTO `country` (`id`, `country_name`) VALUES
(1, 'Afghanistan'),
(2, 'Australia'),
(3, 'Austria'),
(4, 'Bahrain'),
(5, 'Bangladesh'),
(6, 'Belgium'),
(7, 'Brazil'),
(8, 'France'),
(9, 'India'),
(10, 'Pakistan'),
(11, 'United Kingdom'),
(12, 'United States');
 

Create Cities Table:

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `cities` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`country_id` INT(11) NULL DEFAULT NULL,
`city` VARCHAR(255) NULL DEFAULT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=105
;
 

Insert records in Cities Table:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
 
INSERT INTO `cities` (`id`, `country_id`, `city`) VALUES
(1, 1, 'Kabul'),
(2, 1, 'Kandahar'),
(3, 1, 'Herat'),
(4, 1, 'Jalalabad'),
(5, 1, 'Kunduz'),
(6, 1, 'Lashkargah'),
(7, 1, 'Taloqan'),
(8, 2, 'Albury'),
(9, 2, 'Armidale'),
(10, 2, 'Bathurst'),
(11, 2, 'Blue Mountains'),
(12, 2, 'Broken Hill'),
(13, 2, 'Canberra'),
(14, 2, 'Cessnock'),
(15, 2, 'Dubbo'),
(16, 2, 'Melbourne '),
(17, 2, 'Sydney'),
(18, 2, 'Taloqan'),
(19, 3, 'Altenberg'),
(20, 3, 'Amstetten'),
(21, 3, 'Ansfelden'),
(22, 3, 'Bad Aussee'),
(23, 3, 'Bad Ischl'),
(24, 3, 'Lenzing'),
(25, 3, 'Vienna'),
(26, 3, 'Villach'),
(27, 3, 'Wolfsberg'),
(28, 3, 'Zeltweg'),
(29, 3, 'Zwettl'),
(30, 5, 'Dhaka'),
(31, 5, 'Chittagong'),
(32, 5, 'Khulna'),
(33, 5, 'Sylhet'),
(34, 5, 'Rajshahi'),
(35, 5, 'Comilla'),
(36, 5, 'Tongi'),
(37, 5, 'Bogra'),
(38, 5, 'Mymensingh'),
(39, 5, 'Barisal'),
(40, 7, 'Rio de Janeiro'),
(41, 7, 'Brasilia'),
(42, 7, 'Salvador'),
(43, 7, 'Fortaleza'),
(44, 7, 'Belo Horizonte'),
(45, 7, 'Manaus'),
(46, 7, 'Curitiba'),
(47, 7, 'Recife'),
(48, 7, 'Porto Alegre'),
(49, 7, 'Natal'),
(50, 8, 'Paris'),
(51, 8, 'Marseille'),
(52, 8, 'Lyon'),
(53, 8, 'Toulouse'),
(54, 8, 'Nice'),
(55, 8, 'Nantes'),
(56, 8, 'Strasbourg'),
(57, 8, 'Montpellier'),
(58, 8, 'Bordeaux'),
(59, 8, 'Lille'),
(60, 9, 'Mumbai'),
(61, 9, 'Delhi'),
(62, 9, 'Kolkata'),
(63, 9, 'Chennai'),
(64, 9, 'Bangalore'),
(65, 9, 'Hyderabad'),
(66, 9, 'Ahmedabad'),
(67, 9, 'Pune'),
(68, 9, 'Surat'),
(69, 9, 'Jaipur'),
(70, 9, 'Kanpur'),
(71, 9, 'Indore'),
(72, 10, 'Hyderabad'),
(73, 10, 'Larkana'),
(74, 10, 'Sukkur'),
(75, 10, 'Karachi'),
(76, 10, 'Bahawalpur'),
(77, 10, 'Faisalabad'),
(78, 10, 'Lahore'),
(79, 10, 'Multan'),
(80, 10, 'Rawalpindi'),
(81, 10, 'Islamabad'),
(82, 10, 'Peshawar'),
(83, 11, 'Aberdeen'),
(84, 11, 'Belfast'),
(85, 11, 'Birmingham'),
(86, 11, 'Bristol'),
(87, 11, 'Bristol'),
(88, 11, 'Coventry'),
(89, 11, 'Durham'),
(90, 11, 'Leeds'),
(91, 11, 'Liverpool'),
(92, 11, 'Manchester'),
(93, 11, 'London'),
(94, 12, 'New York'),
(95, 12, 'Los Angeles'),
(96, 12, 'Chicago'),
(97, 12, 'Houston'),
(98, 12, 'Phoenix'),
(99, 12, 'Philadelphia'),
(100, 12, 'San Antonio'),
(101, 12, 'San Diego'),
(102, 12, 'Dallas'),
(103, 12, 'San Jose'),
(104, 12, 'Austin');
 

Create Database Connection File: (config.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "demo";
$conn = mysqli_connect($host,$user,$password,$database);
if(!$conn)
{
die(mysqli_error());
}
 
?>
 

HTML: (index.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
<?php
$sql = "select * from country order by country_name ASC";
$rs = mysqli_query($conn,$sql);
?>
<div class="container">
<form action="" method="post">
<div class="country-container">
<label>Country: </label>
<select id="country" name="country_name">
<option value="">Select Country</option>
<?php
while($rows = mysqli_fetch_assoc($rs))
{
echo '<option value="'.$rows['id'].'">'.$rows['country_name'].'</option>';
}
?>
</select>
<img src="img/ajax-loader.gif" id="loader">
</div>
<div class="cities-container">
</div>
<div class="submit-container">
<input type="submit" name="submit" value="submit">
</div>
</form>
</div>
 

In the above code. First I get all the countries and show them in dropdown by using while loop. You might also notice that I used ajax-loader.gif image that have loader id. By default loader will hide. It will only appear when country will change. Then there is cities-container div. I will show cities under cities-container div.

ajax country dropdown

CSS: (style.css)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
body{
font-family:verdana;
background:#ecdeff;
}
 
#country{
width:200px;
height:30px;
}
 
#cities{
width:200px;
height:30px;
margin-top:10px;
margin-left:6px;
}
 
#loader{
display:none;
}
 
label{
width:120px;
display:inline-block;
}
 
.submit-container{
width:23%;
text-align:center;
margin-top:10px;
}
 
input[type=submit]{
color:#ffffff;
background:#ff6d00;
border:1px solid #ff6d00;
padding:5px;
text-align:center;
}
 

jQuery Ajax Request: (index.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
<script type="text/javascript">
$(document).ready(function(){
$("#country").change(function(){
var getCountryID = $(this).val();
if(getCountryID !='')
{
$("#loader").show();
$(".cities-container").html("");
$.ajax({
type:'post',
data:{country_id:getCountryID},
url: 'ajax_request.php',
success:function(returnData){
$("#loader").hide();
$(".cities-container").html(returnData);
}
});
}
else
{
$(".cities-container").html("");
}
})
});
</script>
 

Get Ajax Request And Send Data: (ajax_request.php)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
<?php
require('config.php');
if(isset($_POST['country_id']) && $_POST['country_id'] !='')
{
$countryID = $_POST['country_id'];
$sql = "select * from cities where country_id = '".$countryID."' order by city ASC";
$rs = mysqli_query($conn,$sql);
$numRows = mysqli_num_rows($rs);
if($numRows == 0)
{
echo 'No City Found';
}
else
{
echo '<label>Cities: </label>';
echo '<select name="cities" id="cities">';
while($cities = mysqli_fetch_assoc($rs))
{
echo '<option value="'.$cities['id'].'">'.$cities['city'].'</option>';
}
echo '</select>';
}
}
 
?>
 

Also read:

 

More from WDB24.com

Published by Ahsan Zameer

I am a web developer and love to search new stuff on web. Trying different approaches and ideas to make web developing more interesting and enjoyable.

Leave a Reply

Your email address will not be published. Required fields are marked *