How to Create Drop Down List in PHP with MySQL - onlyxcodes

Saturday 15 July 2023

How to Create Drop Down List in PHP with MySQL

You will learn how to use PHP, Ajax, and MySQL to develop a dynamic drop-down list in this lesson.


You find many e-commerce online applications including country-state-city dropdown UI interfaces. Users pick a country from a drop-down list, after which the state list is generated automatically and the city data is filled in the previously chosen state and country information.


We shall make the same stuff in this post. 


To dynamically fetch data from MySQL into drop-down options without refreshing the page, I used three dependent dropdowns in this post, including Country, State, and City. I also implemented Ajax & jQuery scripts in PHP.


how to create drop down list in php with mysql

Create a Database

Create a database with any name in PHPMyAdmin. I employed the "drop_down_list_php_db" named database in my scenario.


Create Tables in the Database

To begin building this application, first construct three tables in the database called city, state, and country and fill them with fantastical information.


Table: Country


The columns country_id and country_name in this table are added.


Run the SQL statement below to create a country table and fill it with dumping data.


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

Dummy data for country table


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

Table: State


state_id, country_id, and state_name will be the three columns in this table. 


The state drop-down option will be dependent on the country pick option since the country_id column will function as a foreign key and be the same as the country's id in the country table.


Create a state table and insert dummy data into it using the SQL statements listed 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;

Dummy data for state table.


INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'South Australia'),
(2, 1, 'New South Wales'),
(3, 3, 'Alaska'),
(4, 3, 'Alabama');

Table: City


This table will have three columns: state_id, city_id, and city_name


Similar to how it did with the state table, the state_id will act as a foreign key for this table. 


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;

Create a city table and insert dummy data into it using the SQL commands listed below.


INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'Adelaide'),
(2, 1, 'Murray Bridge'),
(3, 2, 'Sydney'),
(4, 2, 'Newcastle'),
(5, 3, 'Angoon'),
(6, 3, 'Aniak'),
(7, 4, 'Abbeville'),
(8, 4, 'Alpine');

View the ERD diagram to see the foreign key relationship between the city, state, and country tables. 


erd diagram to see the foreign key relationship between the city, state, and country tables

Check out this example to see how a dynamic drop-down list works.


check out this example to see how a dynamic drop-down list works

Project File Structure

Employing four PHP scripts, the dynamic drop-down choose option with jQuery Ajax is implemented.


xampp/
├── htdocs/
	├── Drop-Down-List-PHP-MySQL/
		├── bootstrap/
			├── css/
			│	└── bootstrap.min.js
			├── js/
			│	└── bootstrap.bundle.min
			
		├── js/jquery-3.6.0.min.js
		
		dbconfig.php
		index.php
		state.php
		city.php

dbconfig.php – We will establish the database connection in this file.


index.php – Three HTML drop-down lists will be created in this file, and pick options will be dynamically filled through Ajax requests.


state.php – When the index.php file requests a given country name, this file uses a database query to gather the state table data and provide it to the index.php file.


city.php – The index.php file will send requests for certain state names to this file, which will then execute a database query to gather city table data and deliver it to the index.php file.


dbconfig.php

This file contains PHP code that creates a connection to a MySQL database using the PDO extension.


<?php
$db_host="localhost"; //localhost server 
$db_user="root";	//database username
$db_password="";	//database password   
$db_name="drop_down_list_php_db";	//database name

try
{
	$db=new PDO("mysql:host={$db_host};dbname={$db_name}",$db_user,$db_password);
	$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOEXCEPTION $e)
{
	$e->getMessage();
}

?>

index.php

I avoided explaining design codes here and covered the main codes.


I started by including the 'dbconfig.php' database connection file.


The prepare() function then applied a select query statement using the database object $db, and the execute() function executed this statement.


I used a while loop inside the select option tag to loop over all of the country id values and names. 


Rows from the result set are returned by the PDOStatement:: fetch method. PDO::FETCH_ASSOC instructs PDO to return an array value with the columns country_id and country_name as the indexes. 


The $row variable in this case is an array.


<div class="container">
	<div class="row">        
		<div class="col-md-12 mt-5">
		<?php
			
		include 'dbconfig.php';
			
		$select=$db->prepare('SELECT * FROM country');
		$select->execute();
			
		?>
			
		<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
			while($row=$select->fetch(PDO::FETCH_ASSOC))
			{	
			?>
				<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>

Here,


The jQuery change method will trigger the country select tag's id attribute value, country_change, to be activated. 


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

The country_change function is called when a user chooses a country name from the list of available countries, and the $.ajax method pulls all entries from the state table and returns the results in the state select options. 


Next,


The method is the same as before when using the state select box. The state select tag's id attribute value of state_change will initiate the jQuery change method. 


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

When the country change event is finished, all state names are displayed in the state drop-down options. 


When a user chooses a state name from the state drop-down menu, the state_change event is activated, and the $.ajax method retrieves all city data from the city table and returns a result in the city drop-down list.


Implement jQuery Ajax Code

The jQuery and Ajax code in the index.php file starts after the concluding </html> element.


Without having to reload the page, we'll utilize Ajax to retrieve the state and city information from the database. 


country_change event guide,


The change() method appears when the user first chooses the country name option. 


Following that, the ajax() function uses the HTTP POST method to submit the chosen country_id request to the state.php file and receives all state names in an ajax response from the state table.


The success function displays the name of the state after choosing a country id from the state selection. 


Here, the country select option's id property is #country_change, and the .val() method uses this attribute to determine the value of the country id.


<script src="js/jquery-3.6.0.min.js"></script>
<script>
$(document).ready(function(){
	//country change event
	$('#country_change').change(function(){  
		var country = $(this).val();
		$.ajax({
			type: "POST",
			url: "state.php",
			data: "country_id="+country,
			success: function(response)
			{
				$("#state_change").html(response);		
			}
		});
	});
});
</script>

state.php


This file contains PHP code and receives an Ajax HTTP POST request for a country id value. 


This file interacts with the server and runs on the back-end side. 


In this file, the prepare() function uses a PDO select query to choose state records from the state table and dynamically load data when a choice is selected in the country's box.


Here,


Using the $_POST[] method, we retrieve the country_id request, and the isset() function sets this value request. 


This variable request is stored in the new country_id variable.


The country_id variable holds the value of the :cid variable, which is available in the select query, and is bound by the array() method.


The PDO select query is executed by the executes () function.


A row from the result set is returned by the PDOStatement:: fetch method. PDO:: FETCH_ASSOC instructs PDO to deliver an array value with state table columns state_id and state_name as the indexes. An array makes up $row.


The state table's ID and name were then inserted inside an option tag. 


<?php

include 'dbconfig.php';
			
if(isset($_POST['country_id']))
{
	$country_id = $_POST['country_id'];
	
	$select=$db->prepare('SELECT * FROM state WHERE country_id=:cid');
	$select->execute(array(':cid' => $country_id));	
	?>
	<option>-- select state --</option>
	<?php
	while($row=$select->fetch(PDO::FETCH_ASSOC))
	{
	?>
		<option value="<?php echo $row['state_id']; ?>"><?php echo $row['state_name']; ?></option>
	<?php
	}
}

?>

state_change event guide,


The same method is used here, but if a user chooses any state name at that moment, state_change event causes, us to change the name of the change event.  


The city.php file receives all city names in an Ajax response from the city table when the ajax() function sends the selected state_id request there via the HTTP POST method.


The id attribute of the state select option, in this case, is #state_change, and the .val() method uses this attribute to obtain the state id value.


//state change event
$('#state_change').change(function(){
	var state = $(this).val();
	$.ajax({
		type: "POST",
		url: "city.php",
		data: "state_id="+state,
		success: function(response)
		{
			$("#city_result").html(response);		
		}
	});
});

city.php

This file contains PHP code, and an Ajax HTTP POST request sends a state id value request to it. 


This file interacts with the database and runs on the back-end side. 


In this code, the prepare() function uses a PDO select query to choose city records from the city table and dynamically load data when the state option is selected. 


Here,


Using the $_POST[] method, we retrieve a state_id request, and the isset() function sets this value request. 


This variable request is stored in the new state_id variable.


The state_id variable holds the value of the :sid variable, which is available in the select query, and is bound by the array() method.


The PDO select query is executed by the executes () function.


A row from the result set is returned by the PDOStatement:: fetch method. PDO:: FETCH_ASSOC instructs PDO to return array values that are indexed by the city_id and city_name columns of the city table. An array makes up $row.


The city table's id and name were then put inside an option tag.


<?php

include 'dbconfig.php';
			
if(isset($_POST['state_id']))
{
	$state_id = $_POST['state_id'];
	
	$select=$db->prepare('SELECT * FROM city WHERE state_id=:sid');
	$select->execute(array(':sid' => $state_id));	
	?>
	<option>-- select city --</option>
	<?php
	while($row=$select->fetch(PDO::FETCH_ASSOC))
	{
	?>
		<option value="<?php echo $row['city_id']; ?>"><?php echo $row['city_name']; ?></option>
	<?php
	}
}

?>

Download Codes

No comments:

Post a Comment

Post Bottom Ad