Dependent Drop Down using Bootstrap, PHP, MySqli, Ajax, jQuery
We will create a dynamic dependent drop down using jQuery, Ajax,MySqli and PHP. This functionality is used to automatically add dependent data to a drop down list.
This is a very common functionality that is used in Websites. According to the drop-down selection, the dependent data is retrieved from the database and displayed in the next drop-down.
Here we will take an example for country, state, city dropdown to implement a dynamic select box using jQuery, Ajax, MySqli and PHP.
(1). we will create three tables in the database for storing country, state and city data.
Use Below code as sample data.
--
-- Table structure for table `countries`
--
CREATE TABLE `countries` (
`id` int(11) NOT NULL,
`country_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `countries`
--
INSERT INTO `countries` (`id`, `country_name`) VALUES
(1, 'United State America'),
(2, 'India'),
(3, 'United Kingdom');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `countries`
--
ALTER TABLE `countries`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `countries`
--
ALTER TABLE `countries`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;
--
-- Table structure for table `states`
--
CREATE TABLE `states` (
`id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`state_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `states`
--
INSERT INTO `states` (`id`, `country_id`, `state_name`) VALUES
(1, 1, 'Alabama'),
(2, 1, 'Alaska'),
(3, 1, 'Georgia'),
(4, 1, 'New York'),
(5, 2, 'Uttar Pradesh'),
(6, 2, 'Uttarakhand'),
(7, 2, 'Rajasthan'),
(8, 2, 'Punjab'),
(9, 3, 'Belfast'),
(10, 3, 'Conwy'),
(11, 3, 'Denbighshire');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `states`
--
ALTER TABLE `states`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `states`
--
ALTER TABLE `states`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
--
-- Table structure for table `cities`
--
CREATE TABLE `cities` (
`id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
-- Dumping data for table `cities`
--
INSERT INTO `cities` (`id`, `state_id`, `city_name`) VALUES
(1, 1, 'Birmingham'),
(2, 1, 'Montgomery'),
(3, 2, 'Anchorage'),
(4, 2, 'Juneau'),
(5, 3, 'Atlanta'),
(6, 3, 'Columbus'),
(7, 4, 'Hempstead'),
(8, 4, 'Brookhaven'),
(9, 5, 'Noida'),
(10, 5, 'Rampur'),
(11, 6, 'Chamoli'),
(12, 6, 'Dehradun'),
(13, 7, 'Jaipur'),
(14, 7, 'Udaipur'),
(15, 8, 'Amritsar'),
(16, 8, 'Ludhiana'),
(17, 9, 'Belfast'),
(18, 9, 'Newry'),
(19, 10, 'Llanrwst'),
(20, 10, 'Talybont');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `cities`
--
ALTER TABLE `cities`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `cities`
--
ALTER TABLE `cities`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
COMMIT;
(2). We have to create a file for database connection named connection.php.
<?php
error_reporting(0);
$mysql_hostname = "localhost"; # Enter Your Host Name
$mysql_user = "root"; # Enter Your User Name
$mysql_password = ""; # Enter Your Password
$mysql_database = "DATABASE_NAME"; # Enter Your Database Name
$bd = ($GLOBALS["___mysqli_ston"] = mysqli_connect($mysql_hostname, $mysql_user, $mysql_password)) or die("Opps some thing went wrong");
mysqli_select_db( $bd, $mysql_database) or die("wrong");
?>
(3). Now we will create a file named index.php where three dropdowns will be shown. In Country dropdown we will fetch counties table data to show counties name.
<?php include_once('connection.php'); ?>
<!DOCTYPE html>
<html>
<head>
<title>Dependent Drop Down using Bootstrap, PHP , MySqli , Ajax , jQuery</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</head>
<body>
<div class="container">
<h3>Dependent Drop Down using Bootstrap, PHP , MySqli , Ajax , jQuery</h3>
<br />
<form action="" method="post">
<div class = "row">
<!-- Country dropdown -->
<div class="col-md-3">
<label for="country">Country</label>
<select class="form-control" id="country" name="country">
<option value="">Select Country</option>
<?php
$query = "SELECT * FROM countries";
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);
$num_result = mysqli_num_rows($result);
if($num_result > 0)
{
while($row = mysqli_fetch_array($result))
{
?>
<option value = "<?php echo $row['id']; ?>"><?php echo $row['country_name']; ?></option>
<?php
}
}
?>
</select>
</div>
<!-- State dropdown -->
<div class="col-md-3">
<label for="state">State</label>
<select class="form-control" id="state" name="state">
<option value="">Select State</option>
</select>
</div>
<!-- City dropdown -->
<div class="col-md-3">
<label for="city">City</label>
<select class="form-control" id="city" name="city">
<option value="">Select City</option>
</select>
</div>
</div>
</form>
</div>
</body>
</html>
We have included the JQuery and Bootstrap library in the HTML code. The JQuery will be used to work with Ajax call and Bootstrap library will be used for the styling.
(4). Using jQuery Ajax code we will fetch data from the database without reloading the page. Let’s add the below code in the index.php file that sends selected country and selected state value to the server-side script (select.php) via Ajax request when dropdown option value is selected. Get response from the select.php file and display the HTML data to respective dropdown list.
<script>
$(document).ready(function(){
/* For display state drop down for selected country */
$("#country").on("change",function(){
var country = $(this).val();
$.ajax({
url :"select.php",
type:"POST",
cache:false,
data:{country:country},
success:function(data){
$("#state").html(data);
}
});
});
/* For display city drop down for selected state */
$("#state").on("change", function(){
var state = $(this).val();
$.ajax({
url :"select.php",
type:"POST",
cache:false,
data:{state:state},
success:function(data){
$("#city").html(data);
}
});
});
});
</script>
(5). Now, create a PHP file named select.php file. The Ajax request is sent to this PHP file and then retrieves the data such as state list or city list from the database based on the value.
<?php include_once('connection.php');
if (isset($_POST['country']) && !empty($_POST['country']))
{
$query = "SELECT * FROM states WHERE country_id = ".$_POST['country'];
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);
$num_result = mysqli_num_rows($result);
if ($num_result > 0)
{
echo 'Select State';
while($row = mysqli_fetch_array($result))
{
echo ''.$row['state_name'].'';
}
}
else
{
echo 'No State Available';
}
}
if(isset($_POST['state']) && !empty($_POST['state']))
{
$query = "SELECT * FROM cities WHERE state_id = ".$_POST['state'];
$result = mysqli_query($GLOBALS["___mysqli_ston"], $query);
$num_result = mysqli_num_rows($result);
if ($num_result > 0)
{
echo 'Select City';
while($row = mysqli_fetch_array($result))
{
echo ''.$row['city_name'].'';
}
}
else
{
echo 'No City Available';
}
}
?>I hope this article helps you to create a dependent dropdown. Now you can modify the above script accordingly your requirements.
Thanks for visiting Inflay.com .