Site Loader
Get a Quote

Two dependent dropdown list in PHP using MySQL, jQuery and Ajax  :


two Dependent dropdown list in PHP :   Hi readers, here we start with the topic “Two Dependent dropdown list in PhP ” which is mainly used in the form to select from the given list of items like select country, state, city, categories, etc according to the first drop down list. Here in this tutorial, we will explain the details of how to make two dependent dropdowns which populate data from MySQL using jQuery and Ajax in PHP that has been already discussed in the previous post.

Later on, we provide the full source code with snapshot view which makes this concept more clear and concise. In fact, at the end of this tutorial, we will provide the zip folder to download the full source code.

Let’s start the tutorial on two Dependent dropdown list in PHP!

Simple Example of two Dependent Dropdown list in PHP using MySQL, jQuery & Ajax: (Step-by-Step Guide)


Step1: First and foremost step is to create a Database and table directly from the PHPMyAdmin 

open localhost/phpmyadmin using web browser URL and then open sql tab and run the below sql command.

//create database
Create Database dropdown;

//create "State" table
CREATE TABLE IF NOT EXISTS state (
s_id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
stateName varchar(50) NOT NULL,
);

//create "city" table
CREATE TABLE IF NOT EXISTS city(
  c_id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  cityName varchar(50) NOT NULL,
  s_id int(11) NOT NULL,
 );

 


Step 2: After table creation creates an HTML Form containing two drop-down menus using PHP script.

Create a file named “main.php” file and copy the code below and then save it. 

Here in this html file, there are two drop-down menus to select items from the first dropdown list and according to that it will display results in the second dropdown list.

main.php


<?php
require_once 'php/db.php';
?>
<!DOCTYPE html>
<html>
<head>
<title>Two Dependent Drop Downs using PHP, jQuery and Ajax</title>
<script src="js/jquery.min.js"></script>
<script type="text/javascript" src="js/drop.js"></script>
</head>
<body>
<h3>Two Dynamic Dependent Dropdowns to select city according to the state</h3>
<center>
<div>
<h2 style="color:blue;"> Select STATE:</h2>
<select id="state">
    <option value="select" selected="selected">--SELECT STATE--</option>
</select>
</div>
<div>
 <h2 style="color:blue;"> Select CITY:</h2>
 <select id="city">
  <option value="select1" selected="selected">--SELECT--</option>
 </select>
</div>
</center>
<hr />
</body>
</html>


 

Step 3: Create a .js file to use jQuery and Ajax code to populate list without page refresh

Here in this JavaScript file, we have made two functions named “state()” and “change_state()” under which we have used ajax function to populate data from Mysql in a dropdown list without page refresh. Later on, call these two functions when the document is loaded.

Note:

In the beginning, we have to use the below code to empty all the records of dropdown with id=” state” and id=”city”.

 //remove all existing options
  $("#state").empty();
  $("#city").empty();

dropdown.js


function state(){
 $("#state").empty();
 //$("#state").append("<option>---LOADING--</option>");
 
 //ajax function
 $.ajax
 ({
 type: 'POST',
 url: 'php/dropdown.php',
 contentType: 'application/json',
 dataType: 'json',
 cache: false,
 success: function(data){
 $("#state").empty();
 $("#state").append("<option value='0'>--SELECT STATE---</option>");
 $.each(data, function(i,item){
 $("#state").append("<option value=" + data[i].s_id +">"+ data[i].stateName+"</option>");
 
 });
 },
 complete: function(){}
 }); 
 }
 //change funtion
 function change_state(){
// change function of listbox
$('#state').change(function(){
var s_id=$(this).val();
$('#city').empty(); //remove all existing options
$.ajax
 ({
 type: 'POST',
 url: 'php/dropdown1.php',
 dataType: 'text',
 data: {s_id: s_id},
 cache: false,
 success: function(data){
 $('#city').html(data);
 },
 complete: function(){}
 }); 
 });
}
//main javascript code
$(document).ready(function(){
 state();
 change_state();
});

Step 4: Create a PHP file to populate records from MySQL to the dropdown list.

Here in this step first we create a “PHP” named folder in which we make three .php files named db.php, dropdown.php and dropdown1.php . Copy the below code and then save it one by one.

In the db.php file, we simply create and check connection with MySQLi or we can say that it is a configuration file.

Whereas dropdown.php and dropdown1.php file are mainly used to run the SELECT query command to search all records of table state and city of database dropdown using MySQLi. Also, fetch the result and display records accordingly in the dropdown list each time using while loop.

db.php


<?php

// database login
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "dropdown";
 
//Create a Connection
$conn=mysqli_connect($dbhost, $dbuser, $dbpass, $dbname);
 
// Check Connection
if(!$conn){
  die("connection does not establish,error: ". mysqli_connect_error());
}

?>

php/dropdown.php


<?php
require_once 'db.php';

//display records
$sql="select s_id,stateName from state order by stateName";
$display=mysqli_query($conn,$sql);
if(mysqli_num_rows($display))
{ 
  //initialise an array
  $data=array();
 while($rows=mysqli_fetch_assoc($display))
 {
  $data[]=array(
      's_id'=> $rows["s_id"],
      'stateName'=>$rows["stateName"]);
  } 
//encode data in json format
 header('Content-type: application/json');
 echo json_encode($data);
 }

//close the connection
mysqli_close($conn);

?>

php/dropdown1.php


<?php
require_once 'db.php';

//get the value of sate Id using post method
$s_id=$_POST['s_id'];$output='';

//SELECT Query
 $sql="select * from city where s_id=".$s_id." order by cityName";
 $output.='<option value="">SELECT CITY--</option>';

//run the above query
 $display=mysqli_query($conn,$sql);

//display records
 if(mysqli_num_rows($display))
{
 while($rows=mysqli_fetch_assoc($display))
 {
 $output.='<option value="'.$rows["c_id"].'">'.$rows["cityName"].'</option>';
 } 
 echo $output;
 }

//close the connection
mysqli_close($conn);

?>

Snapshot view of Dependent dropdowns in PHP using MySQL, jQuery, and Ajax:

two Dependent dropdown list in PHP
two Dependent dropdown list in PHP

Download file:    dropdown.zip

I hope you would like this tutorial on how we can make two Dependent dropdown list in PHP using MySQL, jQuery, and Ajax!

Post Author: TechieFlair

Leave a Reply

Your email address will not be published.