Dynamic dependent drop-down list using HTML, PHP, MySQL, and AJAX
Introduction
A dependent dropdown list is a common feature used in web applications where the options in one dropdown depend on the value selected in another dropdown.
For example, when a user selects a country from the first dropdown, the second dropdown can automatically display the states or regions belonging to that country. This improves the user experience by displaying only relevant options.
In this guide, we will learn how to create a dynamic dependent dropdown list using HTML, PHP, MySQL, JavaScript, and AJAX. The example uses two dropdown lists: one for countries and another for states.
Note: The original implementation of this article uses PHP’s old mysql_* functions. These functions were deprecated in PHP 5.5 and removed in PHP 7. The implementation below uses MySQLi, which is suitable for modern PHP environments.
Prerequisites
Before implementing the dynamic dependent dropdown, make sure you have:
Basic knowledge of HTML.
Basic knowledge of PHP.
Basic knowledge of MySQL.
Basic understanding of JavaScript and AJAX.
A web server such as Apache or Nginx.
PHP installed on the server.
MySQL or MariaDB installed and running.
A database and user with permission to create and read tables.
jQuery, if you want to use the AJAX approach shown in this guide.
Implementation
1. Create the Database
First, create a database for the application.
CREATE DATABASE demo;
Select the database:
USE demo;
USE demo;
2. Create the Countries Table
Create a table to store country information:
CREATE TABLE ls_countries (
country_id INT NOT NULL AUTO_INCREMENT,
sortname VARCHAR(3) NOT NULL,
name VARCHAR(150) NOT NULL,
phonecode INT NOT NULL,
status INT NOT NULL, P
RIMARY KEY (country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;The country_id column uniquely identifies each country.
3. Create the States Table
Next, create a table to store states or regions:
CREATE TABLE ls_states (
state_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
country_id INT NOT NULL,
status INT NOT NULL,
PRIMARY KEY (state_id),
FOREIGN KEY (country_id) REFERENCES ls_countries(country_id)
);The country_id column connects each state with its corresponding country.
This relationship allows us to retrieve states based on the country selected by the user.
4. Create the Database Connection
Create a file named db.php to handle the database connection.
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "demo";
$conn = new mysqli($host, $user, $password, $database);
if ($conn->connect_error) {
die("Database connection failed: " . $conn->connect_error);
}Using a separate connection file makes the application easier to maintain because the same database connection can be reused by multiple PHP files.
5. Create the Country Dropdown
Create a file named dropdown-ajax.php.
Start by including the database connection:
<?php require_once "db.php"; ?>
Retrieve the available countries:
<?php
$sql = "SELECT country_id, name
FROM ls_countries
WHERE status = 1
ORDER BY name";
$result = $conn->query($sql);
?>Create the dropdown:
<select id="country" onchange="fetch_select(this.value)">
<option value="">Select country</option>
<?php while ($row = $result->fetch_assoc()): ?>
<option value="<?= htmlspecialchars($row['country_id']) ?>">
<?= htmlspecialchars($row['name']) ?>
</option>
<?php endwhile; ?>
</select>
<select id="new_select">
<option value="">Select state</option>
</select>The first dropdown displays the available countries.
When the user selects a country, the fetch_select() JavaScript function is called.
6. Add AJAX Functionality
Include jQuery in the HTML page:
function fetch_select(countryId) {
if (!countryId) {
document.getElementById("new_select").innerHTML =
'<option value="">Select state</option>';
return;
}
$.ajax({
type: "POST",
url: "fetch_data.php",
data: {
get_option: countryId
},
success: function(response) {
document.getElementById("new_select").innerHTML = response;
},
error: function() {
document.getElementById("new_select").innerHTML =
'<option value="">Unable to load states</option>';
}
});
}The AJAX request sends the selected country_id to fetch_data.php.
The response from fetch_data.php is then inserted into the second dropdown.
7. Create the AJAX PHP File
Create another file named fetch_data.php.
Include the database connection:
7. Create the AJAX PHP File
Create another file named fetch_data.php.
Include the database connection:
<?php require_once "db.php";
Check whether a country ID was received:
if (isset($_POST['get_option'])) {
$countryId = filter_input(
INPUT_POST,
'get_option',
FILTER_VALIDATE_INT
);
if (!$countryId) {
exit;
}
// Continue with the database query...
}8. Retrieve States for the Selected Country
Use a prepared statement to safely retrieve the states:
$stmt = $conn->prepare(
"SELECT state_id, name
FROM ls_states
WHERE country_id = ? AND status = 1
ORDER BY name"
);
$stmt->bind_param("i", $countryId);
$stmt->execute();
$result = $stmt->get_result();Prepared statements help prevent SQL injection and are preferable to directly inserting user input into SQL queries.
9. Return the States as Dropdown Options
Loop through the results and generate the <option> elements:
echo '<option value="">Select state</option>';
while ($row = $result->fetch_assoc()) {
echo '<option value="' .
htmlspecialchars($row['state_id']) .
'">' .
htmlspecialchars($row['name']) .
'</option>';
}The complete fetch_data.php can be written as:
<?php
require_once "db.php";
if (!isset($_POST['get_option'])) {
exit;
}
$countryId = filter_input(
INPUT_POST,
'get_option',
FILTER_VALIDATE_INT
);
if (!$countryId) {
exit;
}
$stmt = $conn->prepare(
"SELECT state_id, name
FROM ls_states
WHERE country_id = ? AND status = 1
ORDER BY name"
);
$stmt->bind_param("i", $countryId);
$stmt->execute();
$result = $stmt->get_result();
echo '<option value="">Select state</option>';
while ($row = $result->fetch_assoc()) {
echo '<option value="' .
htmlspecialchars($row['state_id']) .
'">' .
htmlspecialchars($row['name']) .
'</option>';
}
$stmt->close();
$conn->close();Complete Example
The complete dropdown-ajax.php file can be structured as follows:
<?php
require_once "db.php";
$sql = "SELECT country_id, name
FROM ls_countries
WHERE status = 1
ORDER BY name";
$result = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Dependent Dropdown</title>
<script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
<script>
function fetch_select(countryId) {
if (!countryId) {
document.getElementById("new_select").innerHTML =
'<option value="">Select state</option>';
return;
}
$.ajax({
type: "POST",
url: "fetch_data.php",
data: {
get_option: countryId
},
success: function(response) {
document.getElementById("new_select").innerHTML =
response;
},
error: function() {
document.getElementById("new_select").innerHTML =
'<option value="">Unable to load states</option>';
}
});
}
</script>
</head>
<body>
<label for="country">Country:</label>
<select id="country" onchange="fetch_select(this.value)">
<option value="">Select country</option>
<?php while ($row = $result->fetch_assoc()): ?>
<option value="<?= htmlspecialchars($row['country_id']) ?>">
<?= htmlspecialchars($row['name']) ?>
</option>
<?php endwhile; ?>
</select>
<label for="new_select">State:</label>
<select id="new_select">
<option value="">Select state</option>
</select>
</body>
</html>How the Dynamic Dropdown Works
The complete process works as follows:
- The PHP application retrieves the list of countries from MySQL.
- The countries are displayed in the first dropdown.
- The user selects a country.
- The
onchangeevent calls the JavaScriptfetch_select()function. - AJAX sends the selected
country_idtofetch_data.php. - PHP receives the country ID.
- PHP queries the
ls_statestable for matching states. - The matching states are returned as HTML
<option>elements. - JavaScript inserts the response into the second dropdown.
- The user can select a state associated with the selected country.
This process happens without reloading the entire web page.
Security Considerations
When implementing dependent dropdowns in a production application, consider the following:
- Avoid the deprecated
mysql_*PHP functions. - Use MySQLi or PDO for database connections.
- Use prepared statements for queries involving user input.
- Validate and sanitize incoming values.
- Escape database output before displaying it in HTML.
- Do not expose database credentials in publicly accessible files.
- Use HTTPS when transmitting application data.
- Restrict database users to the permissions they actually need.
FAQs
1. What is a dependent dropdown list?
A dependent dropdown list is a dropdown whose available options depend on the value selected in another dropdown.
For example, selecting India in the country dropdown can display only Indian states in the state dropdown.
2. Why is AJAX used for dependent dropdowns?
AJAX allows the application to retrieve the required data from the server without refreshing the entire webpage.
When a user selects a country, only the state information needs to be requested and updated.
3. Can this implementation be used without jQuery?
Yes. The same functionality can be implemented using JavaScript’s native fetch() API or XMLHttpRequest. jQuery is used in this example because the original implementation uses jQuery AJAX.
Conclusion
A dynamic dependent dropdown is useful when the options in one field depend on the selection made in another field. Using HTML, PHP, MySQL, JavaScript, and AJAX, developers can dynamically retrieve and display related data without reloading the entire page.
In this example, countries are displayed in the first dropdown, and the states associated with the selected country are dynamically retrieved from MySQL and displayed in the second dropdown.
For modern PHP applications, it is important to replace the deprecated mysql_* functions used in older implementations with MySQLi or PDO, along with prepared statements and appropriate input validation.
Related Articles
- Send Email Using HTML Templates in CodeIgniter – Learn how to send emails using HTML templates with CodeIgniter.
Send Email Using HTML Templates in CodeIgniter - How to Install Nginx and Let’s Encrypt SSL with HTML, Docker and Ubuntu 20.04 – Learn how to configure Nginx, Docker, and Let’s Encrypt SSL on Ubuntu.
How to Install Nginx and Let’s Encrypt SSL with HTML, Docker and Ubuntu 20.04 - Install PHP 7.2 Mcrypt Module on Ubuntu 18.04 – Learn how to install and configure the PHP Mcrypt module on Ubuntu.
Install PHP 7.2 Mcrypt Module on Ubuntu 18.04
Thanks for sharing. This article is useful to learn Dependent Dropdown Using Ajax in PHP.