Dynamic Dependent Select Box Using PHP OOP Ajax - onlyxcodes

Monday 26 July 2021

Dynamic Dependent Select Box Using PHP OOP Ajax

In this article, I will show you how to use PHP OOP Ajax to develop a dynamic dependent select box. This feature has frequently been used to append dependent data to a dropdown list dynamically.


This is a very popular component used in web projects such as product selection by category in a shopping cart website and others based on custom requirements.


We'll use jQuery, Ajax, and PHP OOP programming to construct a dynamic select box for the country, state, and city dropdown.


All countries are first retrieved from the database and displayed in the dropdown menu. When we choose a country, the states in that country are retrieved from the database and displayed in the state dropdown. Likewise, when a state is chosen, the cities in that state are retrieved from the database and displayed in the city dropdown.


dynamic dependent select box using php oop ajax

Create Tables in the Database

To begin, we'll build three tables in the database to store data for the country, state, and city.

 

Table: country


There will be two columns in this table: country_id and country_name. Run the following SQL statement to create a country table and add the sample dumping data.


CREATE TABLE `country` (
  `country_id` int(11) NOT NULL,
  `country_name` varchar(14) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dumping data for table country


INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'India'),
(3, 'United States');

Table: state


There will be three columns in this table: state_id, country_id, and state_name. The country_id column will act as a foreign key and will be the same as the country id in the country table, making the state select box dependent on the country select box.


To construct a state table and insert the dumped data into it, use the SQL query below. 


CREATE TABLE `state` (
  `state_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `state_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dumping data for table state


INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'Gujarat'),
(2, 1, 'Maharastra'),
(3, 3, 'Alaska'),
(4, 3, 'Alabama');

Table: city


There will be three columns in this table: city_id, state_id, and city_name. state_id will serve as a foreign key for this table, just like it did for the state table. 


To construct a city table and import the dumped data, execute the following command.


CREATE TABLE `city` (
  `city_id` int(11) NOT NULL,
  `state_id` int(11) NOT NULL,
  `city_name` varchar(15) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dumping data for table city


INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'Vadodra'),
(2, 1, 'Surat'),
(3, 2, 'Pune'),
(4, 2, 'Mumbai'),
(5, 3, 'Angoon'),
(6, 3, 'Aniak'),
(7, 4, 'Abbeville'),
(8, 4, 'Alpine');

The interrelationship between the country, state, and city tables is depicted in this ERD.


relationship between the country, state, and city in erd

Below is a practical demonstration of how a dynamic dependent select box works.


dynamic dependent select box using php oop ajax - three dropdown list - onchange - populating the - based on the selected value of first

Project File Structure

The dynamic select box using Ajax jQuery is implemented using four PHP files.


xampp/
├── htdocs/
	├── Dynamic-Dependent-Select-Box-PHP-OOP-Ajax/
		├── bootstrap/
			├── css/
			│	└── bootstrap.min.js
			├── js/
			│	└── bootstrap.bundle.min
		
		dropdown.php
		index.php
		state.php
		city.php

dropdown.php – The PHP Class that we shall create is called dropdown. This PHP class will be used to implement Object-Oriented Programming in PHP. 


The PHP Class will wrap the values from the database's country, state, and city tables. We'll define the values that will be saved in a variable form in the SQL database.


index.php – involves creating three select boxes and sending an ajax call to populate the box's options.


state.php – Get specific country name requests from index.php, query the database using the dropdown.php file, and collect the state table data and returning it to index.php


city.php – index.php receives specific state name requests, and the dropdown.php file queries the database, obtaining the city table data, and giving it back to index.php 


index.php

Only the main codes are discussed in this file.


See rows 10 and 14, where I used the bootstrap CSS and bundle JS files to improve the overall UI of the page.


In row 18, I used the CDN technique to include the jQuery library. The $.ajax method in this library can be used to build a dynamic dependent select box. 


Row no. 43 is where the PHP code begins. The dropdow.php file, which contains the Dropdown class, was included.


After that, I construct a $obj named object of the Dropdown class, and if $obj->fetchCountry is invoked, all country table records are passed to the rows array variable.


Inside the select option tag, I used a foreach loop to iterate through all of the country id values and names.


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

    <!-- Bootstrap CSS -->
	
    <link rel="stylesheet" href="bootstrap/css/bootstrap.min.css" />
	
	<!-- Bootstrap Bundle with Popper -->
	
    <script src="bootstrap/js/bootstrap.bundle.min.js"></script>
	
	<!-- jQuery library -->
	
	<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>

    <title>Dynamic Dependent Select Box PHP OOP Ajax : Onlyxcodes</title>
	
  </head>
  <body>
    <nav class="navbar navbar-expand-lg navbar-dark" style="background-color: #3f51b5;">
	  <div class="container-fluid">
		<a class="navbar-brand" href="https://www.onlyxcodes.com/">Onlyxcodes</a>
		<button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarSupportedContent" aria-controls="navbarSupportedContent" aria-expanded="false" aria-label="Toggle navigation">
		  <span class="navbar-toggler-icon"></span>
		</button>
		<div class="collapse navbar-collapse" id="navbarSupportedContent">
		  <ul class="navbar-nav me-auto mb-2 mb-lg-0">
			<li class="nav-item">
			  <a class="nav-link active" aria-current="page" href="https://www.onlyxcodes.com/2021/07/dynamic-dependent-select-box-php-oop-ajax.html">Back to Tutorial</a>
			</li>
		  </ul>
		</div>
	  </div>
	</nav>
	
    <div class="container">
        <div class="row">        
			<div class="col-md-12 mt-5">
			<?php
			
			include 'dropdown.php';
			
			$obj = new Dropdown();
			
			$rows = $obj->fetchCountry();
			
			?>
			<label class="form-label" style="font-size:20px; font-style:bold;">Select Country</label>
			<select id="country_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
				<option>-- select country --</option>
			  <?php
				if(!empty($rows))
				{
					foreach($rows as $row)
					{
					?>
						<option value="<?php echo $row['country_id']; ?>"><?php echo $row['country_name']; ?></option>	
				<?php
					}
				}
			  ?>
			  
			</select>

			<label class="form-label" style="font-size:20px; font-style:bold;">Select State</label>
			<select id="state_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
			  <option>-- select state --</option>
			</select>
	
			<label class="form-label" style="font-size:20px; font-style:bold;">Select City</label>
			<select id="city_result" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">
			  <option>-- select city --</option>
			</select>

            </div>
        </div>
    </div>

  </body>
</html>

dropdown.php file codes


public function fetchCountry()
{
	$data = null;

	$query = "SELECT * FROM country";

	if ($sql = $this->connection->query($query)) 
	{
		while ($rows = mysqli_fetch_assoc($sql)) 
		{
			$data[] = $rows;
		}
	}

	return $data;
}

Here,


The id attribute value country_change of the country select tag will be triggered by the jQuery change method.


<select id="country_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">

If a user selects any country name from the country select options, the country_change method is called, and the $.ajax method retrieves all entries from the state table and returns the response in the state select box options. 


In the state select box, the approach is exactly the same as before. The jQuery change method will be triggered by the state select tag id attribute value state_change.


<select id="state_change" class="form-select form-select-lg mb-3" aria-label=".form-select-lg example">

All state names appear in the state select options when the country change event is completed.


The state_change event is triggered when a user selects a state name from the state select options, and the $.ajax method obtains all city data from the city table and returns a result in the city select box options. 


Implement jQuery Ajax Code

After the closing </html> tag in the index.php file, the jQuery and Ajax code begins.


We'll use Ajax to get the state and city from the database without having to reload the page.


country_change event guide,


When the user selects a country name, the country_change response is activated, which causes the ajax( ) function to send the selected country id request to the state.php file, which uses the HTTP POST method to retrieve all state names in an ajax response from the state table.


Finally, by selecting a country id from the state dropdown, the success function displays the state name. 


The response array result is handled by the key variable in the foreach loop. In the options tag, the key variable receives the state table id and state name.


Because when the state_change event triggers, we will get a specific state id if a user selects any state name. We'll also be able to quickly retrieve city records by utilizing a specific state table id. 

#country_change is the id attribute of the country select box, and the .val() method is used to retrieve the country id value. 


<script>
// country dependent ajax
 $(document).on("change","#country_change", function(e){
	e.preventDefault();
		
	var country = $("#country_change").val();	
		
	$.ajax({
        type: "POST",
        url: "state.php",
		dataType: "json",
        data: "country_id="+country,
        success: function(response)
        {
			var stateBody = "";
			stateBody = "<option>-- select state --</option>"
			for(var key in response)
			{
				stateBody += "<option value="+ response[key]['state_id'] +">"+ response[key]['state_name'] +"</option>";
				$("#state_change").html(stateBody);
			}		
        }
    });
 });
</script>

state.php


This file runs in the background and communicates with the server using an ajax HTTP POST request. The country id value request is received by this file and sent to the Dropdown class.


The dropdown class, which holds all database-related stuff, is included here.


The $obj variable represents a Dropdown class object.


If $obj->fetchState($country id) is executed successfully. We assign the result to the $rows variable, and this variable returns the state table record, which records the response in JSON format because of the JSON encode() function. 


<?php

if(isset($_POST['country_id']))
{
	$country_id = $_POST['country_id'];
	
	include 'dropdown.php';
	
	$obj = new Dropdown();
	
	$rows = $obj->fetchState($country_id);

	echo json_encode($rows);	
}

?>

dropdown.php file codes


I generated a Dropdown name class in this file. This class establishes the database connection and performs database query operations with the help of a few functions.


__construct() — This function creates a database connection with the MySQLi extension within the try/catch block. If an exception is thrown, it can be handled quickly.


The fetchState($country id) function returns data from the state table for a given country id. 


<?php 

Class Dropdown
{
	private $host = 'localhost';
	private $username = 'root';
	private $password = '';
	private $dbname = 'dynamic_dependent_php_oop';
	private $connection;

	//establish connection
	public function __construct()
	{
		try 
		{
			$this->connection = new mysqli($this->host, $this->username, $this->password, $this->dbname);
		} 
		catch (Exception $e) 
		{
			echo "Connection error " . $e->getMessage();
		}
	}

	
	//fetch all country records from database
	public function fetchCountry()
	{
		$data = null;

		$query = "SELECT * FROM country";

		if ($sql = $this->connection->query($query)) 
		{
			while ($rows = mysqli_fetch_assoc($sql)) 
			{
				$data[] = $rows;
			}
		}

		return $data;
	}
	
	//fetch state records from database depend upon country id
	public function fetchState($country_id)
	{
		$data = null;

		$query = "SELECT * FROM state WHERE country_id='".$country_id."' ";

		if ($sql = $this->connection->query($query)) 
		{
			while ($rows = mysqli_fetch_assoc($sql)) 
			{
				$data[] = $rows;
			}
		}

		return $data;
	}
	
	//fetch city records from database depend upon state id
	public function fetchCity($state_id)
	{
		//
	}

}

 ?>

state_change event guide,


The same approach applies here, but we modify the name of the change event if a user selects any state name at that time state_change event causes.


The ajax( ) function sends the selected state id to the 'city.php' file using the HTTP POST method to receive all city names in the ajax response from the city table. 


The success of displaying the city name within the city drop-down based on the state id selected. 


The response array result is handled by the key variable in the foreach loop. The options tag assigns the city name to the key variable.


The .val() method is used to obtain the state id value from the #state_change property of the state select box.


<script>
// state dependent ajax
 $(document).on("change","#state_change", function(e){
	e.preventDefault();
		
	var state = $("#state_change").val();	
		
	$.ajax({
        type: "POST",
        url: "city.php",
		dataType: "json",
        data: "state_id="+state,
        success: function(response)
        {
			var cityBody = "";
			cityBody = "<option>-- select city --</option>"
			for(var key in response)
			{
				cityBody += "<option>"+ response[key]['city_name'] +"</option>";
				$("#city_result").html(cityBody);
			}		
        }
    });
 });
</script>

city.php


This file executes in the background and interacts with the server using an ajax HTTP POST request. The state id value request is received by this file and sent to the Dropdown class.


The dropdown class, which holds all database-related information, was also put here.


The $obj variable represents a Dropdown class object.


If $obj->fetchCity($country id) is executed successfully. We save the result in the $rows variable, which returns the city records response in JSON format because this variable encoded by json_encode() function.


<?php

if(isset($_POST['state_id']))
{
	$state_id = $_POST['state_id'];
	
	include 'dropdown.php';
	
	$obj = new Dropdown();
	
	$rows = $obj->fetchCity($state_id);

	echo json_encode($rows);	
}

?>

dropdown.php file codes


The fetchCity($state id) function returns data from the city table for a given state id.


<?php 

Class Dropdown
{
	private $host = 'localhost';
	private $username = 'root';
	private $password = '';
	private $dbname = 'dynamic_dependent_php_oop';
	private $connection;

	//establish connection
	public function __construct()
	{
		try 
		{
			$this->connection = new mysqli($this->host, $this->username, $this->password, $this->dbname);
		} 
		catch (Exception $e) 
		{
			echo "Connection error " . $e->getMessage();
		}
	}

	//fetch all country records from database
	public function fetchCountry()
	{
		//
	}
	
	//fetch state records from database depend upon country id
	public function fetchState($country_id)
	{
		//
	}
	
	//fetch city records from database depend upon state id
	public function fetchCity($state_id)
	{
		$data = null;

		$query = "SELECT * FROM city WHERE state_id='".$state_id."' ";

		if ($sql = $this->connection->query($query)) 
		{
			while ($rows = mysqli_fetch_assoc($sql)) 
			{
				$data[] = $rows;
			}
		}

		return $data;
	}

}

 ?>

Learn More:

CRUD APP using PHP OOP PDO MySQL and Ajax


Download Codes

4 comments:

Post Bottom Ad