Site icon WDB24

Ajax dropdown list from Database using PHP and jQuery

Ahsan Zameer
php featured image

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:

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.

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:

Exit mobile version