When you have multiple dropdowns and each dropdown has dependent value based on the selection of other dropdowns. This may be an obscure approach to filter values in each dropdown dynamically. In this post, I am here with the dropdown value filter based on another dropdown. During this step, the form will be refreshed without jQuery Ajax. So, I will be using the jQuery Ajax technique for preventing the default form reload. If you are a complete beginner and don’t know how to start with jQuery Ajax in PHP then go through my previous post.
Object-Oriented Programming in PHP
I’ll be creating a new PHP project and today, I will show you the Object-Oriented Approach (OOPs) in PHP. The OOP is a good programming approach in which everything is treated in the form of an object. So here, I am not dealing more with the OOPs concept with you because I am assuming you are aware of the core of the OOPs. So, let’s move to the project directly.
Load Data From MySQL Database in PHP Using jQuery Ajax
Dependent Dropdown Filter in PHP
In this project, I will create three dropdowns. All dropdowns will have dynamic values and it will be loading from the database.
- The first one will have the programming language name.
- The second will contain the framework and will be filtered only on the selection of the language from the first dropdown.
- In the last dropdown, I will be populating the latest version of the framework which will be based on the selection of the framework dropdown.
So, in the first instance, create a new database in MySQL with any name.
Database Schema For Dropdown Filter
Here, is the database schema. I have three tables as shown below-
- language
- framework and
- version
All tables are related to each other. In the first table, the language_id field is primary and it is referencing to the field (language_id) in the framework table.
Similarly, the framework_id in the second table is referencing to the framework_id in the version table.
Here, is the complete database structure with dumped values. Just open the phpMyAdmin and create a new database and then paste the below code there.
-- phpMyAdmin SQL Dump
-- version 4.8.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 16, 2019 at 09:11 PM
-- Server version: 10.1.34-MariaDB
-- PHP Version: 7.2.7
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `dropdown_filter`
--
-- --------------------------------------------------------
--
-- Table structure for table `framework`
--
CREATE TABLE `framework` (
`framework_id` int(11) NOT NULL,
`framework_name` varchar(100) NOT NULL,
`language_id` int(11) NOT NULL,
`created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `framework`
--
INSERT INTO `framework` (`framework_id`, `framework_name`, `language_id`, `created_on`) VALUES
(1, 'Laravel', 1, '2019-08-15 17:01:35'),
(2, 'CodeIgniter', 1, '2019-08-15 17:01:35'),
(3, 'Symfony', 1, '2019-08-15 17:01:35'),
(4, 'CakePHP', 1, '2019-08-15 17:01:35'),
(5, 'Yii', 1, '2019-08-15 17:01:35'),
(6, 'Zend Framework', 1, '2019-08-15 17:01:35'),
(7, 'Phalcon', 1, '2019-08-15 17:01:35'),
(8, 'FuelPHP', 1, '2019-08-15 17:01:35'),
(9, 'PHPixie', 1, '2019-08-15 17:01:35'),
(10, 'Slim', 1, '2019-08-15 17:01:35'),
(11, 'dJango', 2, '2019-08-15 17:01:35'),
(12, 'Pyramid ', 2, '2019-08-15 17:01:35'),
(13, 'TurboGears', 2, '2019-08-15 17:01:35'),
(14, 'Web2py', 2, '2019-08-15 17:01:35'),
(15, 'Flask', 2, '2019-08-15 17:01:35'),
(16, 'Bottle', 2, '2019-08-15 17:01:35'),
(17, 'CherryPy', 2, '2019-08-15 17:01:35'),
(18, 'Sanic', 2, '2019-08-15 17:01:35'),
(19, 'Tornado', 2, '2019-08-15 17:01:35'),
(20, 'Dash', 2, '2019-08-15 17:01:35');
-- --------------------------------------------------------
--
-- Table structure for table `language`
--
CREATE TABLE `language` (
`language_id` int(11) NOT NULL,
`language_name` varchar(100) NOT NULL,
`added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `language`
--
INSERT INTO `language` (`language_id`, `language_name`, `added_on`) VALUES
(1, 'PHP', '2019-08-15 16:58:18'),
(2, 'Python', '2019-08-15 16:58:18');
-- --------------------------------------------------------
--
-- Table structure for table `version`
--
CREATE TABLE `version` (
`version_id` int(10) NOT NULL,
`version` varchar(5) NOT NULL,
`framework_id` int(10) NOT NULL,
`added_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `version`
--
INSERT INTO `version` (`version_id`, `version`, `framework_id`, `added_on`) VALUES
(1, '5.8', 1, '2019-08-15 18:51:17'),
(2, '3.1.1', 2, '2019-08-15 18:51:17'),
(3, '4.6.4', 3, '2019-08-15 19:01:32'),
(4, '3.8', 4, '2019-08-15 19:01:32'),
(5, '2.0.2', 5, '2019-08-15 19:01:32'),
(6, '2.4.9', 6, '2019-08-15 19:01:32'),
(7, '4.0', 7, '2019-08-15 19:01:32'),
(8, '1.8.2', 8, '2019-08-15 19:01:32'),
(9, '2.x', 9, '2019-08-15 19:01:32'),
(10, '4.1.0', 10, '2019-08-15 19:01:32'),
(11, '2.2.4', 11, '2019-08-15 19:01:32'),
(12, '1.10', 12, '2019-08-15 19:01:32'),
(13, '2.4.0', 13, '2019-08-16 16:20:34'),
(14, '2.18', 14, '2019-08-16 16:21:24'),
(15, '1.1.x', 15, '2019-08-16 16:23:22'),
(16, '0.12', 16, '2019-08-16 16:24:19');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `framework`
--
ALTER TABLE `framework`
ADD PRIMARY KEY (`framework_id`);
--
-- Indexes for table `language`
--
ALTER TABLE `language`
ADD PRIMARY KEY (`language_id`);
--
-- Indexes for table `version`
--
ALTER TABLE `version`
ADD PRIMARY KEY (`version_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `framework`
--
ALTER TABLE `framework`
MODIFY `framework_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;
--
-- AUTO_INCREMENT for table `language`
--
ALTER TABLE `language`
MODIFY `language_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `version`
--
ALTER TABLE `version`
MODIFY `version_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;PHP Form Handling Using Ajax jQuery without Form Refresh
Create a New PHP Project For Dropdown List Filter
- Create a new project inside the htdocs (if you have XAMPP) or www (if you have Wamp Server.
- Open your project in your favorite editor.
Create a MySQL Database Connection Using OOPs Approach
- Create a new file with the name db-config.php.
- Paste the below code there.
<?php
class DBController {
private $hostname = "localhost";
private $username = "root";
private $password = "root";
private $db = "dropdown_filter";
// ----------- [ Creating connection ] ---------------
public function connect() {
$conn = new mysqli($this->hostname, $this->username, $this->password, $this->db)or die("Database connection error." . $conn->connect_error);
return $conn;
}
// ---------- [ Closing connection ] -----------------
public function close($conn) {
$conn->close();
}
}
?>- In the above code, I have created a class with the name DBController.
- Inside the class, I have declared variables as private so that It cannot be accessed outside of this class.
- In the next step, I have created a function (connect) in which I have created an object of mysqli() class. In this function, I have passed the hostname, username of the database, password of the database, and the database name.
- For accessing the private variable inside the function, PHP provides this keyword. We cannot access the private variables directly inside the function.
- As we know a function returns some value so here, I have returned the connection object.
- Similarly, I have created another function for closing the connection.
How to Implement jQuery DataTable in PHP MySQL
Create a Form Using Bootstrap
- Now, create a new PHP file with named index.php. In this file,
<!doctype html>
<html lang="en">
<head>
<title>Dropdown Filter in PHP Using jQuery AJAX </title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-6 col-lg-6 col-md-6 col-sm-12 col-12 m-auto d-block shadow p-5">
<form id="filterForm">
<label for="selectLang">Language </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectLang">
<option selected disabled>Select Language </option>
</select>
</div>
<label for="selectFrame">Framework </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectFrame">
<option>Select Framework </option>
</select>
</div>
<label for="selectVersion">Current Version </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectVersion">
<option>Select Version </option>
</select>
</div>
</form>
</div>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>
</body>
</html>
Save and execute the above code to see the result as shown below.
PHP File Upload Using jQuery and Ajax
PHP Function for Populating Dropdown Values
- Create a new PHP file. In this file, we will write the PHP functions for populating the values from the database.
- So that, our PHP script and the HTML part will be separate and using the function call, I will be retrieving the data into the Dropdown list.
Populate Language Name in First Dropdown
In my case, I have created the PHP file with the name data-controller.php. In this function, I will write only the PHP scripts.
- Here, I have included the database connection file which is db-config.php.
- Again created a class named DataController in the data-controller.php file.
- In this class, I will create the functions for retrieving the values from the database tables.
- So, look at the first function named getLanguages(). In this function, I have retrieved all languages from the language table.
<?php
include_once './db-config.php';
class DataController {
// ------------ [ Language Listing ] ------------------------
public function getLanguages() {
$data = array();
$db = new DBController();
$conn = $db->connect();
$sql = "SELECT * FROM language";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}
}
?>- The above function will return the languages which have been fetched through the database.
- Now, this function will need to be called where you will require the languages name.
- So, we have to display the language name in the first dropdown of the index.php file.
Call PHP Function in PHP File
- In order to call the above PHP function which is inside a class, you’ll need to create an object of that class. Then by using that object, we’ll call the function.
- Navigate to the index.php file and paste the below code at the top of the file.
<?php
include_once './data-controller.php';
$data = new DataController();
$languages = $data->getLanguages();
?>- Here, I have included the PHP file (data-controller.php) in which I have written the function.
- Then created an object of the class which is declared inside the data-controller.php file.
- Lastly, called the function through the object of the class.
- Now, we will display the language in the dropdown using foreach loop which has returned in the call back of the function.
- So our index.php file will be like this.
<?php
include_once './data-controller.php';
$data = new DataController();
$languages = $data->getLanguages();
?>
<!doctype html>
<html lang="en">
<head>
<title>Dropdown Filter in PHP Using jQuery AJAX </title>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-6 col-lg-6 col-md-6 col-sm-12 col-12 m-auto d-block shadow p-5">
<form id="filterForm">
<label for="selectLang">Language </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectLang">
<option selected disabled>Select Language </option>
<?php foreach($languages as $language): ?>
<option value="<?php echo $language['language_id']; ?> "><?php echo $language['language_name']; ?> </option>
<?php endforeach; ?>
</select>
</div>
<label for="selectFrame">Framework </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectFrame">
<option>Select Framework </option>
</select>
</div>
<label for="selectVersion">Current Version </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectVersion">
<option>Select Version </option>
</select>
</div>
</form>
</div>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js" integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js" integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous"></script>
</body>
</html>
jQuery Ajax Method For Populating Values in Dropdown List
Now, it’s time to send the Ajax request to the server so that it will get data from the database table on the basis of dropdown change.
First of all, I will create an Ajax function for the first dropdown change event which is for the language. That means when you’ll select any language, It will send the request to the server with the selected language and will return the framework of that language.
- Open the index.php file and paste the below code at the bottom after the CDNs.
<script>
$(document).ready(function() {
// ------------------ [ Language dropdown Change Event ] --------------
$("#selectLang").change(function() {
var languageId = $(this).val();
$(this).fadeIn();
$.ajax({
url: 'process-controller.php',
type: 'POST',
data: {
langId: languageId
},
dataType: "JSON",
success: function(result) {
var items = "";
$("#selectFrame").empty();
$("#selectVersion").empty();
$("#selectFrame").append(
"<option selected disabled> Select Framework </option>");
$("#selectVersion").append(
"<option selected disabled> Select Version </option>");
$.each(result, function(i, item) {
$("#selectFrame").append("<option value='" + item
.framework_id + "'>" + item.framework_name +
"</option>");
});
}
});
});
});
</script>In the above code, the ajax sends the request to a URL. In OOPs concept, the Ajax request cannot send directly to the PHP function. Therefore, I’ll create a new PHP file in which, I will handle the Ajax request by calling the other PHP function in this file.
So, in the data-controller.php file, just paste this code for retrieving the frameworks from the framework table based on the language_id.
// ------------- [ Framework Listing ] -----------------------
public function frameworkListing($languageId) {
$data = array();
$db = new DBController();
$conn = $db->connect();
$sql = "SELECT * FROM framework WHERE language_id = '$languageId'";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}Then, create a new file named process-controller.php and paste the below code.
- When you’ll select the first dropdown which is language. Then the selection of the dropdown item will send the request to this file (process-controller.php) and here in this file, I have called a function which is defined in another PHP file (data-controller.php).
<?php
require_once './data-controller.php';
if(isset($_POST['langId'])) {
$langId = $_POST['langId'];
$dController = new DataController();
$frameworks = $dController->frameworkListing($langId);
echo json_encode($frameworks);
}
?>- After processing the data, It will return the response as a result in the JSON format.
- Similarly, on the selection of the second dropdown, it will send the request and will return the response.
Here is the full source code of this project.
index.php
<?php
include_once './data-controller.php';
$data = new DataController();
$languages = $data->getLanguages();
?>
<!doctype html>
<html lang="en">
<head>
<title>Dropdown Filter in PHP Using jQuery AJAX </title>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
</head>
<body>
<div class="container mt-5">
<div class="row">
<div class="col-xl-6 col-lg-6 col-md-6 col-sm-12 col-12 m-auto d-block shadow p-5">
<form id="filterForm">
<label for="selectLang">Language </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectLang">
<option selected disabled>Select Language </option>
<?php foreach($languages as $language): ?>
<option value="<?php echo $language['language_id']; ?> ">
<?php echo $language['language_name']; ?> </option>
<?php endforeach; ?>
</select>
</div>
<label for="selectFrame">Framework </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectFrame">
<option>Select Framework </option>
</select>
</div>
<label for="selectVersion">Current Version </label>
<div class="form-group">
<select class="form-control form-control-md" id="selectVersion">
<option>Select Version </option>
</select>
</div>
</form>
</div>
</div>
</div>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js"
integrity="sha384-vFJXuSJphROIrBnz7yo7oB41mKfc8JzQZiCq4NCceLEaO4IHwicKwpJf9c9IpFgh" crossorigin="anonymous">
</script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js"
integrity="sha384-alpBpkh1PFOepccYVYDB4do5UnbKysX5WZXm3XxPqe5iKTfUKjNkCk9SaVuEZflJ" crossorigin="anonymous">
</script>
<script>
$(document).ready(function() {
// ------------------ [ Language dropdown Change Event ] --------------
$("#selectLang").change(function() {
var languageId = $(this).val();
$("#selectFrame").fadeIn('slow');
$.ajax({
url: 'process-controller.php',
type: 'POST',
data: {
langId: languageId
},
dataType: "JSON",
success: function(result) {
var items = "";
$("#selectFrame").empty();
$("#selectVersion").empty();
$("#selectFrame").append(
"<option selected disabled> Select Framework </option>");
$("#selectVersion").append(
"<option selected disabled> Select Version </option>");
$.each(result, function(i, item) {
$("#selectFrame").append("<option value='" + item
.framework_id + "'>" + item.framework_name +
"</option>");
});
}
});
});
// ------------- [ Framework Dropdown Change ] ---------------
$("#selectFrame").change(function() {
var frameworkId = $(this).val();
$(this).fadeIn();
$.ajax({
url: './process-controller.php',
type: 'POST',
data: {
framId: frameworkId
},
dataType: 'JSON',
success: function(result) {
var version = "";
$("#selectVersion").empty();
$.each(result, function(i, version) {
$("#selectVersion").append("<option>" + version.version +
"</option>");
});
}
});
});
});
</script>
</body>
</html>data-controller.php
<?php
include_once './db-config.php';
class DataController {
// ------------ [ Language Listing ] ------------------------
public function getLanguages() {
$data = array();
$db = new DBController();
$conn = $db->connect();
$sql = "SELECT * FROM language";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}
// ------------- [ Framework Listing ] -----------------------
public function frameworkListing($languageId) {
$data = array();
$db = new DBController();
$conn = $db->connect();
$sql = "SELECT * FROM framework WHERE language_id = '$languageId'";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}
// --------------- [ Version Listing ] -------------------------
public function versionListing($frameworkId) {
$data = array();
$db = new DBController();
$conn = $db->connect();
$sql = "SELECT * FROM version WHERE framework_id = '$frameworkId'";
$result = $conn->query($sql);
if($result->num_rows > 0) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
$db->close($conn);
return $data;
}
}
?>process-controller.php
<?php
require_once './data-controller.php';
if(isset($_POST['langId'])) {
$langId = $_POST['langId'];
$dController = new DataController();
$frameworks = $dController->frameworkListing($langId);
echo json_encode($frameworks);
}
elseif(isset($_POST['framId'])) {
$framId = $_POST['framId'];
$dController = new DataController();
$version = $dController->versionListing($framId);
echo json_encode($version);
}
?>Here, is the final result of the above application.
Conclusion
I have tried with very simple examples so that you will understand. I hope this post will help you to populate the multiple dropdown values based on the selection of any dropdown without refreshing the form. Also, I have implemented the OOPs approach to write the PHP script in a better way so that your designs and functionalities both will be separate from each other. If you will have any problem or suggestions regarding this tutorial, then please let me know by putting your comment below.
Thank you so much for the tutorial it is very well explained.
Will you please tell me about dependent filters e.g if I select state then it display data after that regarding that state proper city filter activated after city sub tehsil filter activated
This tutorial was fantastic and very well explained. I am a relative newbie to php, jquery and ajax and was wondering if you could use the above example to show how to INSERT the selected values in to another table. I am sure there is an easy way but your code is quite elegant and I would like to see how you would implement it.
Hi, simply read the dropdown selected value to a PHP variable like –
$value = $_POST['dropdown_item_name'];Then, just pass the value to the
INSERTstatement.Thank you for the reply. Much appreciated. Another quick question if you don’t mind.
I am trying to use the same methodology you’ve used using the process controller and the data controller files to check if a value entered in ‘text’ field (let’s say Insured Name) already exists in the Company table.
So, what’s your issue?