Dependent Drop-down List in PHP using jQuery AJAX with Country State Example
by Vincy. Last modified on July 12th, 2022.
Do you think implementing a dependent dropdown list is a complex job? If so, this article will let you change your thoughts and encourage you to achieve it on your own.
A dependent dropdown list is the best solution if you want to get the user input in sequential order. The Dependent dropdown is a useful feature when the dropdowns data are inter-related.
The country-state interdependency is a classical example scenario for telling about the necessity of a dependent dropdown. We can increase the length of the dependency ladder to 3, 4 and so on.
Once I had a chance to implement this for more than 8 dependent dropdowns. I did this for a search filter in material management software. If you implement this once for a pair of dependent dropdown then the concept is the same to do it for a lengthy sequence.
We can say many examples of such dependent entities, Categories-Sub-categories-Products in a shopping cart application, Department-Courses and more.
Below screenshot shows the dynamic dependent data fetch is in progress. The loader displayed in the dependent state dropdown indicates it.
Once the endpoint got the data, then the script will return HTML response with dependent data. Then, the AJAX success callback will load the response into the target. In the following screenshot, we can see the state results based on the selected country.
What is inside?
- Advantages of having dependent drop-downs
- Dependent dropdown wireframe
- About this example
- File structure
- HTML form with dependent dropdown
- How to load country dependent drop-down list via jQuery AJAX
- PHP code to read country-state from database
- PHP classes to generate database queries and execute operations
- Country-State Database Structure and Data
- jQuery dependent dropdown example output
Advantages of having dependent drop downs
In many scenarios, the dependent dropdowns lead to having advantages for the application. Some of those advantages are,
- The dynamic loading of dependent data will increase performance.
- Loading dependent data based on the selected item will optimize the fetch process.
- It will give relevant options to the user which will avoid confusion.
- It helps to narrow down the resource and make the data filtering process simple.
Dependent dropdown wireframe
This is a basic idea we are going to put in place in the PHP example code. There will be two dropdowns for countries and states.
This wireframe will help us to move forward with UI design and dependent data loading.
About this example
The dependent dropdowns are a best practice while dealing with interdependent combo fields.
Creating a dependent dropdown with PHP and jQuery is very simple. This example will explain how to create a dependent dropdown ladder.
The dropdown options are dynamic from the database.
A country dropdown shows initial options on loading the page. On choosing a country, it loads the options on the state dropdown field.
I use jQuery AJAX to get the dependent options for the state dropdown based on the selected country.
In a previous tutorial, we have seen an example to get the dependent results based on the typed keyword. See this link if you want the code to filter country name using jQuery autocomplete.
File structure
- index.php – Landing page displays countries list in a dropdown.
- get-country-state-ep.php – AJAX endpoint to get state records depends on the country selected.
- CountryState.php – This is a Model class. It contains functions to get the countries and the dependent states result.
- DataSource.php – It is Generic DAO. It handles database operations.
- vendor – contains jQuery library files used to execute the AJAX script.
- schema.sql – Database script with country-state database structure and sample data.
HTML form with dependent dropdown
This HTML is for showing the country dropdown with data on page load. It also shows the state dropdown with no options.
As I say already, the country dropdown data are from the database. This HTML contains embedded PHP code to connect the database to read the country results.
On changing the country dropdown, it sends the selected country id to PHP via AJAX. PHP code uses this id as a reference to get the dependent state database results.
A jQuery function contains this script to handle the AJAX-based request.
While requesting dependent data, a loader will state that the request is in progress. On the AJAX success callback, it loads the dependent state results.
<html>
<head>
<TITLE>jQuery Dependent DropDown List - Countries and States</TITLE>
<head>
<link href="./assets/css/style.css" rel="stylesheet" type="text/css" />
<script src="./vendor/jquery/jquery-3.2.1.min.js" type="text/javascript"></script>
</head>
<body>
<div class="frmDronpDown">
<div class="row">
<label>Country:</label><br /> <select name="country"
id="country-list" class="demoInputBox"
onChange="getState(this.value);">
<option value="">Select Country</option>
<?php
foreach ($countryResult as $country) {
?>
<option value="<?php echo $country["id"]; ?>"><?php echo $country["country_name"]; ?></option>
<?php
}
?>
</select>
</div>
<div class="row">
<label>State:</label><br /> <select name="state"
id="state-list" class="demoInputBox">
<option value="">Select State</option>
</select> <img id="loader" src="./images/loader.gif" />
</div>
</div>
</body>
</html>
And the PHP code to get the initial country result is as follows.
The CountryState.php is the Model class create for this example. It contains functions to prepare the queries to fetch all country data on page load. Also, it contains to fetch the country-based state database results.
<?php
namespace Phppot;
use Phppot\CountryState;
require_once __DIR__ . '/Model/CountryState.php';
$countryState = new CountryState();
$countryResult = $countryState->getAllCountry();
?>
How to load country dependent drop-down list via jQuery AJAX
The dependent dropdown is about to get loaded with the related data on the chosen value or keyword. The jQuery AJAX script created for handling this data loading is below.
The getState() has the parameter value containing the country id. This parameter is for constructing the AJAX request data.
PHP will receive this data to prepare the SELECT query. It will get the records from the state database table on the sent country id.
By executing this query, it will return an array of states. In PHP, it iterates this array to prepare the HTML to supply the data for the state drop-down list.
In the AJAX end, the dependent dropdown will get loaded with the response HTML returned from the PHP code.
function getState(val) {
$("#loader").show();
$.ajax({
type: "POST",
url: "./ajax/get-country-state-ep.php",
data:'country_id='+val,
success: function(data){
$("#state-list").html(data);
$("#loader").hide();
}
});
}
PHP code to read country-state from database
This PHP code is for handling the request sent via jQuery AJAX. The get-country-state-ep.php PHP endpoint contains this script.
It receives the country_id sent via an AJAX call using the POST request method.
It connects the CountryState Model to invoke the getStateByCountrId method. This method fetches the state records for the selected country.
In this example, I used MySQLi prepared statements for executing the database operations.
The query execution will result in an array of dependent state results. This PHP code forms state dropdown HTML options and returns it as the response to the AJAX request.
<?php
namespace Phppot;
use Phppot\CountryState;
if (! empty($_POST["country_id"])) {
$countryId = $_POST["country_id"];
require_once __DIR__ . '/../Model/CountryState.php';
$countryState = new CountryState();
$stateResult = $countryState->getStateByCountrId($countryId);
?>
<option value="">Select State</option>
<?php
foreach ($stateResult as $state) {
?>
<option value="<?php echo $state["id"]; ?>"><?php echo $state["name"]; ?></option>
<?php
}
}
?>
PHP classes to generate database queries and execute operations
This example has two PHP classes CountryState.php, DataSource.php.
The CountryState.php has the function to get all countries from the database. The getStateByCountrId() is to get the states based on its country_id parameter.
These functions generate queries and params and request database access. The following code shows this PHP class.
CountryState.php
<?php
namespace Phppot;
use Phppot\DataSource;
class CountryState
{
private $ds;
function __construct()
{
require_once __DIR__ . './../lib/DataSource.php';
$this->ds = new DataSource();
}
/**
* to get the country record set
*
* @return array result record
*/
public function getAllCountry()
{
$query = "SELECT * FROM country";
$result = $this->ds->select($query);
return $result;
}
/**
* to get the state record based on the country_id
*
* @param string $countryId
* @return array result record
*/
public function getStateByCountrId($countryId)
{
$query = "SELECT * FROM states WHERE country_id = ?";
$paramType = 'd';
$paramArray = array(
$countryId
);
$result = $this->ds->select($query, $paramType, $paramArray);
return $result;
}
}
The DataSource is to connect the database and perform the requested query operations.
We can get the database connect object on instantiating this class. The below code shows methods select(), bindQueryParams() to prepare query and bind params.
DataSource.php
<?php
namespace Phppot;
/**
* Generic datasource class for handling DB operations.
* Uses MySqli and PreparedStatements.
*
* @version 2.3
*/
class DataSource
{
// PHP 7.1.0 visibility modifiers are allowed for class constants.
// when using above 7.1.0, declare the below constants as private
const HOST = 'localhost';
const USERNAME = 'root';
const PASSWORD = '';
const DATABASENAME = 'phpsamples';
private $conn;
/**
* PHP implicitly takes care of cleanup for default connection types.
* So no need to worry about closing the connection.
*
* Singletons not required in PHP as there is no
* concept of shared memory.
* Every object lives only for a request.
*
* Keeping things simple and that works!
*/
function __construct()
{
$this->conn = $this->getConnection();
}
/**
* If connection object is needed use this method and get access to it.
* Otherwise, use the below methods for insert / update / etc.
*
* @return \mysqli
*/
public function getConnection()
{
$conn = new \mysqli(self::HOST, self::USERNAME, self::PASSWORD, self::DATABASENAME);
if (mysqli_connect_errno()) {
trigger_error("Problem with connecting to database.");
}
$conn->set_charset("utf8");
return $conn;
}
/**
* To get database results
* @param string $query
* @param string $paramType
* @param array $paramArray
* @return array
*/
public function select($query, $paramType="", $paramArray=array())
{
$stmt = $this->conn->prepare($query);
if(!empty($paramType) && !empty($paramArray)) {
$this->bindQueryParams($stmt, $paramType, $paramArray);
}
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$resultset[] = $row;
}
}
if (! empty($resultset)) {
return $resultset;
}
}
/**
* 1. Prepares parameter binding
* 2. Bind prameters to the sql statement
* @param string $stmt
* @param string $paramType
* @param array $paramArray
*/
public function bindQueryParams($stmt, $paramType, $paramArray=array())
{
$paramValueReference[] = & $paramType;
for ($i = 0; $i < count($paramArray); $i ++) {
$paramValueReference[] = & $paramArray[$i];
}
call_user_func_array(array(
$stmt,
'bind_param'
), $paramValueReference);
}
}
Country-State Database Structure and Data
This script contains the create statement, data dump for the country and state tables. It also contains the Alter queries to set the required table index.
Import this script while setting this example in your PHP development environment.
-- -- Database: `phpsamples` -- -- -------------------------------------------------------- -- -- Table structure for table `country` -- CREATE TABLE `country` ( `id` int(11) NOT NULL, `country_name` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `country` -- INSERT INTO `country` (`id`, `country_name`) VALUES (1, 'Brazil'), (2, 'China'), (3, 'France'), (4, 'India'), (5, 'USA'); -- -------------------------------------------------------- -- -- Table structure for table `states` -- CREATE TABLE `states` ( `id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `country_id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `states` -- INSERT INTO `states` (`id`, `name`, `country_id`) VALUES (1, 'Sao Paulo', 1), (2, 'Rio de Janeiro', 1), (3, 'Ceara', 1), (4, 'Santa Catarina', 1), (5, 'Espirito Santo', 1), (6, 'Beijing', 2), (7, 'Hebei', 2), (8, 'Jiangsu', 2), (9, 'Guangdong', 2), (10, 'Guangdong', 2), (11, 'Ile-de-France', 3), (12, 'Midi-Pyrenees', 3), (13, 'Picardie', 3), (14, 'Franche-Comte', 3), (15, 'Alsace', 3), (16, 'Haryana', 4), (17, 'Andhra Pradesh', 4), (18, 'Delhi', 4), (19, 'Tamil Nadu', 4), (20, 'Uttar Pradesh', 4), (21, 'California', 5), (22, 'Iowa', 5), (23, 'New York', 5), (24, 'New Jersey', 5), (25, 'Massachusetts', 5); -- -- Indexes for dumped tables -- -- -- Indexes for table `country` -- ALTER TABLE `country` ADD PRIMARY KEY (`id`); -- -- Indexes for table `states` -- ALTER TABLE `states` ADD PRIMARY KEY (`id`), ADD KEY `country_id` (`country_id`); -- -- AUTO_INCREMENT for table `states` -- ALTER TABLE `states` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=26; -- -- Constraints for table `states` -- ALTER TABLE `states` ADD CONSTRAINT `states_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`); COMMIT;
I generated this ERD using phpMyAdmin. It shows the interrelationship between the country state database tables.
Conclusion
In a sequence of the dependent dropdowns, the later depends on the value chosen on the previous one.
The source of the data is the database for this country-state example. We can also use a file or pre-defined array as a data storage. You can customize this example code to point to a different data source (file or array) to get the dependent data.
The AJAX-based request handling will give a good user experience to your application. Getting data from the database on a need basis will improvise the performance. So, this code will give you an efficient solid route to build a dependent dropdown ladder as your own.
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.
Hi, I'm Vincy. I help build websites, grow businesses, big and small. If that is what you want, contact me.
I'm currently available for freelance work.