To create a dynamic dependent Select box with Ajax
Prerequisite: Basic Understanding of HTML, CSS, JavaScript, jQuery, PHP and MySQL.
In this project:
- We will create three HTML select boxes i.e. for country, state and cities.
- Countries will be fetched from the countries table in “db” database.
- On selecting a country, http request will be generated with the help of ajax to fetch all the states of that particular country.
- Similarly on selecting a state, again a http request will be generated with the help of ajax to fetch all the cities of that selected state.
- CSS styling will be done on requirement basis.
index.php ⬇
<?php
include_once 'includes/conn.php';
$sql = "SELECT * FROM countries WHERE status = 1 ORDER BY name";
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$countries[$row['id']] = $row['name'];
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title>AJAX DYNAMIC DEPENDENT SELECT</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
</head>
<body>
<div class="main" style="padding: 10px">
<div class="row">
<div class="col-md-2">
<select class="form-control" id="country__select">
<option value="">--SELECT COUNTRY--</option>
<?php
foreach($countries as $key => $val){
echo "<option value='".$key."'>".$val."</option>";
}
?>
</select>
</div>
<div class="col-md-2">
<select class="form-control" id="state__select">
<option>--SELECT STATE--</option>
</select>
</div>
<div class="col-md-2">
<select class="form-control" id="city__select">
<option>--SELECT CITY--</option>
</select>
</div>
</div>
</div>
</body>
<script>
$(document).ready(function(){
$("#country__select").change(function(){
$.ajax({
url: 'list.php',
type: 'POST',
data: {country_id:$(this).val()},
success: function(data) {
var result = JSON.parse(data);
var html = "<option>--SELECT STATE--</option>";
if(result.length > 0){
for(var i = 0; i < result.length; i++){
html += "<option value='"+result[i]['id']+"'>"+result[i]['name']+"</option>";
}
}
$("#state__select").html(html);
$("#city__select").html("<option>--SELECT CITY--</option>");
},
error: function(){
$("#state__select").html("<option>--SELECT STATE--</option>");
}
});
});
$("#state__select").change(function(){
$.ajax({
url: 'list.php',
type: 'POST',
data: {state_id:$(this).val()},
success: function(data) {
var result = JSON.parse(data);
var html = "<option>--SELECT CITY--</option>";
if(result.length > 0){
for(var i = 0; i < result.length; i++){
html += "<option value='"+result[i]['id']+"'>"+result[i]['name']+"</option>";
}
}
$("#city__select").html(html);
},
error: function(){
$("#city__select").html("<option>--SELECT CITY--</option>");
}
});
});
});
</script>
</html> Start the localhost services and run index.php in your web browser to get the following output.
While searching the output will be:
On selecting country “India”, states of India are loaded into the state select box.
On selecting state “Punjab”, cities of Punjab are loaded into the cities select box.
Explanation of Index.php
- HTML Selectbox is created with ID
“country__select”.Options are fetched from countries table which is already created in database “db”.
$sql = "SELECT * FROM countries WHERE status = 1 ORDER BY name";
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$countries[$row['id']] = $row['name'];
}
}
<select class="form-control" id="country__select">
<option value="">--SELECT COUNTRY--</option>
<?php
foreach($countries as $key => $val){
echo "<option value='".$key."'>".$val."</option>";
}
?>
</select> - A selectbox with ID
“state_select”is created, but no list is provided or displayed here.
<select class="form-control" id="state__select">
<option>--SELECT STATE--</option>
</select>
- Similary, an empty selectbox with ID
“city__select”is also created.
<select class="form-control" id="city__select">
<option>--SELECT CITY--</option>
</select> - To use jQuery Functions jQuery file is included in the head tag. You can use either an online link or download it from the
jQuery.comand can include in the page accordingly.
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>- All the jQuery functions are put inside the document ready function to make sure that all the events are executed after the DOM gets ready for it.
$(document).ready(function(){
…
});
- Search is performed on change event using jQuery.
“#country__select”ID name is used to define change method. Current value is stored in value variable. AJAX is further defined to fetch the states name from database.
$("#country__select").change(function(){
var value = $(this).val();
// Ajax method…
});
- jQuery Ajax method defines the following parameters.
|
url: |
url parameter specifies the url you want to request. |
|
type: |
HTTP GET and POST are used to request the data from the server. |
|
data: |
It specifies the data to send with the request. |
|
success: |
Success is invoked upon successful completion of Ajax request. |
|
error: |
Error is invoked upon the failure of Ajax request. |
$.ajax({
url: 'list.php',
type: 'POST',
data: {country_id:value},
success: function(data) {
var result = JSON.parse(data);
var html = "<option>--SELECT STATE--</option>";
if(result.length > 0){
for(var i = 0; i < result.length; i++){
html += "<option value='"+result[i]['id']+"'>"+result[i]['name']+"</option>";
}
}
$("#state__select").html(html);
$("#city__select").html("<option>--SELECT CITY--</option>");
},
error: function(){
$("#state__select").html("<option>--SELECT STATE--</option>");
}
}); Current value of Country select box is sent to list.php using HTTP POST method.
On successful fetching of JSON encoded states list, the result JSON is parsed using JSON.parse method.
The resulted array has length > 0, then the list is iterated and the name are stored in html variable in option tag.
HTML data is assigned to “#state_select” html block.
On error, “#state_select” block is assigned default option tag.
- Similary when you select any state from state selectbox, the following jQuery event will be fired to fetch the cities list.
$("#state__select").change(function(){
var value = $(this).val();
// AJAX CODE…
});- Likewise states, cities are also fetched and displayed in a similar manner using JQuery ajax as follows:
$.ajax({
url: 'list.php',
type: 'POST',
data: {state_id:value},
success: function(data) {
var result = JSON.parse(data);
var html = "<option>--SELECT CITY--</option>";
if(result.length > 0){
for(var i = 0; i < result.length; i++){
html += "<option value='"+result[i]['id']+"'>"+result[i]['name']+"</option>";
}
}
$("#city__select").html(html);
},
error: function(){
$("#city__select").html("<option>--SELECT CITY--</option>");
}
}); On error, the city_select selectbox is assigned with default option to be displayed.
Request through Ajax is sent to the list.php file and the corresponding file is discussed below:
list.php⬇
<?php
include "includes/conn.php";
if(isset($_POST['country_id']) && $_POST['country_id'] != ""){
$country_id = $_POST['country_id'];
$states = $arr = array();
if(is_numeric($country_id) && $country_id > 0){
$sql = "SELECT id, name FROM states WHERE country_id = '".$country_id."' AND status = '1' ORDER BY name";
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$arr['id'] = $row['id'];
$arr['name'] = $row['name'];
$states[] = $arr;
}
}
}
echo json_encode($states);
}
if(isset($_POST['state_id']) && $_POST['state_id'] != ""){
$state_id = $_POST['state_id'];
$cities = $arr = array();
if(is_numeric($state_id) && $state_id > 0){
$sql = "SELECT id, name FROM cities WHERE state_id = '".$state_id."' AND status = '1' ORDER BY name";
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$arr['id'] = $row['id'];
$arr['name'] = $row['name'];
$cities[] = $arr;
}
}
}
echo json_encode($cities);
}
?>Explanation of list.php
- Connection file conn.php is included using PHP file include method. It connects to the “db” database.
include "includes/conn.php";- Below we have mentioned the code for
Conn.phpfile:
<?php
$con = new mysqli("localhost","root","","db");
if ($con->connect_errno) {
echo "Failed to connect to MySQL: ".$con->connect_error;
exit();
}
?>If there is any error in connecting to the database, error message is displayed on the screen and exit method is called to stop further execution of the php code. Otherwise, connection is successfully built and further execution is proceeded.
- If country_id POST variable is set then the control comes inside the IF BLOCK.
In case of fetching states,
if(isset($_POST['country_id']) && $_POST['country_id'] != ""){
// SOME CODE HERE…
}
In case of fetching cities,
if(isset($_POST['state_id']) && $_POST['state_id'] != ""){
// SOME CODE HERE…
}
You can write your code of instructions inside the if block, that you want to perform according to the requirement. In our case, we will fetch the list of states from the database and will encode them using json_encode() method
- A table named as countries, states and cities are created in the MySQL Database db to which we are connected. Listing of these tables is also done according to our requirement. For example: the table we have created is as shown below :
Countries table:
States table:
Cities table:
We have done some dummy entries just to explain the concept.
- States are fetched using mysql SELECT STATEMENT. If number of rows is greater than 0, then the resultant array is iterated using while loop and the values are assigned to $states array.
if(is_numeric($country_id) && $country_id > 0){
$sql = "SELECT id, name FROM states WHERE country_id = '".$country_id."' AND status = '1' ORDER BY name";
$exe = mysqli_query($con, $sql);
$num_rows = mysqli_num_rows($exe);
if($num_rows > 0){
while($row = mysqli_fetch_assoc($exe)){
$arr['id'] = $row['id'];
$arr['name'] = $row['name'];
$states[] = $arr;
}
}
}
echo json_encode($states); Similar instructions are wriiten for cities listing as well.
Summary
In this project, we have learned to create dynamic and dependent selectboxes with the help of AJAX. We have taken an example of Countries, States and Cities Selectbox. Countries are fetched and displayed on page loading only while State and City values are dependent on Country and State correspondingly.
Tables are created with values in MySQL and all the names are fetched dynamically from these tables only. We have used AJAX jQuery to send HTTP POST request to get the list from these tables.
