Dependent Dropdown in php and mysql using jQuery Ajax – Detailed with Source Code
How to make Dependent dropdown in jQuery Ajax Mysql PHP

Dependent Dropdown in php and mysql using jQuery Ajax – Detailed with Source Code

Introduction

In this article, we are going to study how we can add dependent dropdown in PHP, Ajax, and Mysql. Some people feel adding dependent dropdown is a daunting task but it’s quite easy and with little practice, you can easily understand it’s implementation.

Benefits

The most obvious benefit it will provide your website is that it will allow increase performance of the website as only required data is loaded on fetch and also it will give a better user experience which will give a premium feel to your website or application.

Dependent dropdown using PHP, Mysql and Ajax

Getting Database Ready

In this article we are going to take classic example of States and Countries so it’s easy for users to understand

Database Schema

--
-- Database: `dependentdropdown`
--

-- --------------------------------------------------------

--
-- Table structure for table `tbcountries`
--

CREATE TABLE `tbcountries` (
  `id` int(11) NOT NULL,
  `countryname` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbcountries`
--

INSERT INTO `tbcountries` (`id`, `countryname`) VALUES
(1, 'India'),
(2, 'USA');

-- --------------------------------------------------------

--
-- Table structure for table `tbstates`
--

CREATE TABLE `tbstates` (
  `id` int(11) NOT NULL,
  `statename` varchar(255) NOT NULL,
  `countryid` int(11) DEFAULT NULL COMMENT 'Foriegn key for Country'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tbstates`
--

INSERT INTO `tbstates` (`id`, `statename`, `countryid`) VALUES
(1, 'Punjab', 1),
(2, 'Uttar Pardesh', 1),
(3, 'Bihar', 1),
(4, 'Sikkim', 1),
(5, 'Alabama', 2),
(6, 'California', 2),
(7, 'Georgia', 2);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbcountries`
--
ALTER TABLE `tbcountries`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tbstates`
--
ALTER TABLE `tbstates`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbcountries`
--
ALTER TABLE `tbcountries`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `tbstates`
--
ALTER TABLE `tbstates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

In this, we have created two tables – tbcountries and tbstates. tbcountries has 2 fields – id (primary key) and countryname ,tbstates has 3 fields – id (primarykey), statename and countryid(foreiegn key from tbcountries table)

PHP and HTML Code to show dropdowns

Connecting to database config.php

<?php
$conn=mysqli_connect('localhost','root','','dependentdropdown');
?>

Here we will be using bootstrap for designing part and jquery to call our ajax request for fetching states based on country.

<!doctype html>
<html lang="en">
  <head>
    <!-- Required meta tags -->
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">

    <title>Dependent Dropdown in Jquery</title>
  </head>
  <body>
    <div class="container">
      <h3 class="text-center">Dependent Dropdown using PHP, Mysql and Ajax</h3>
   <form method="post" class="mt-5" action="">
      <div class="row">
        <div class="col-md-3 offset-md-3">
          <select required class="form-control" id="country" name="country">
            <option value="">Select Country</option>
            <?php
            include 'config.php';
            $query=mysqli_query($conn,"select * from tbcountries");
            while($row=mysqli_fetch_assoc($query)):
            ?>
            <option value="<?php echo $row['id']; ?>"><?php echo $row['countryname']; ?></option>
          <?php endwhile; ?>
          </select>
        </div>
        <div class="col-md-3">
           <select required class="form-control" id="state" name="country">
            <option value="">Select Country first</option>
          </select>
        </div>
      </div>
   </form>
 </div>

    <!-- Jquery Script -->
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js" integrity="sha256-CSXorXvZcTkaix6Yvo6HppcZGetbYMGWSFlBw8HfCJo=" crossorigin="anonymous"></script>
    <script type="text/javascript">
      $('#country').click(function(){
        //Get selected Country ID
        var countryid = $(this).val();
       $.ajax({
        type      : 'POST',
        url: 'getstates.php',
        data      : 'country='+countryid, //pass country data
        success   : function(data)
        {
          $('#state').html(data);
        }
        });
      })
    </script>
  </body>
</html>

Here we have created two dropdowns for Country and States. The Country dropdown is prefilled but States dropdown is empty and will be filled with states once you select country.

Ajax Code to fetch states

<script type="text/javascript">
      $('#country').click(function(){
        //Get selected Country ID
        var countryid = $(this).val();
       $.ajax({
        type      : 'POST',
        url: 'getstates.php',
        data      : 'country='+countryid, //pass country data
        success   : function(data)
        {
          $('#state').html(data);
        }
        });
      })
    </script>

Here we get states when country is clicked. We have assigned #country and #state ids to country and state dropdowns respectively. When the Country is clicked we have first fetch the id of country selected. And than ajax request is sent using $.ajax method . We hit url getstates.php with ajax and pass countryid to it. After we get response from getstates.php it is added to state dropdown.

PHP Code to fetch states from country – getstates.php

<?php
include 'config.php';
if(isset($_POST['country'])):
	//Get Country ID
$country=$_POST['country'];
//Query to get states based on country
$query=mysqli_query($conn,"select * from tbstates where countryid=$country");
//Get states returned
$checkcountries=mysqli_num_rows($query);
//If 1 or more states returned than loop and add options
if($checkcountries>0):
	echo "<option value=''>Select State Now</option>";
	while($row=mysqli_fetch_assoc($query)): ?>
		<option value="<?php echo $row['id']; ?>"><?php echo $row['statename']; ?></option>
	<?php endwhile; 
else:
	//if no states found
	echo "<option value=''>No States Found</option>";
endif;
	endif;
?>

In getstates.php we get the countryid and than based on that we run a mysql query . Firstly we check if states exists for that country by using mysqli_num_rows function. If no states are found we send response “No sattes found” else we fill it with options of states and send it as response. Now in jQuery we use html() function on #state select dropdown to fill states data.

Demo and Source Code Download

Dependent Dropdown using PHP and Ajax Demo

Anmol Mathneja

I am Web Developer who loves sharing his work experiences with people. I provide services like Website Development, Website Designing and Digital Marketing. My areas of expertise include Core PHP, Codeigniter, Laravel, Wordpress, jQuery, Node JS, HTML, CSS.

Leave a Reply