Hi-ya Technoz, In this tutorial we are going to learn Dynamic Dependent Dropdown or sometimes you may also called it relational select boxes. In this case we going to implement using procedural PHP and off course MySQLi. Sometimes we need to create select boxes which must dependent on each other. For example, If first select box contain countries then it will decide which state will be in the next select box. Similarly, After selecting the state select box it will decide which cities in the next dropdown.
1. Create Database for Dynamic Dependent Dropdown
To store the Relational select boxes values a table need to create in the database. So lets create the database by using following queries. The following SQL creates category table which consist of id, category_name,category_link, parent_id and sort_order details.
CREATE TABLE `category` ( `category_id` int(10) UNSIGNED NOT NULL, `category_name` varchar(50) COLLATE latin1_general_ci NOT NULL, `category_link` varchar(255) COLLATE latin1_general_ci NOT NULL, `parent_id` int(10) UNSIGNED NOT NULL DEFAULT '0', `sort_order` int(11) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
Dumping data for table `category to make relational select boxes
Now, lets fill some records into the table ‘category’
INSERT INTO `category` (`category_id`, `category_name`, `category_link`, `parent_id`, `sort_order`) VALUES (1, 'Home', 'home', 0, 0), (2, 'Tutorials', 'tutorials', 0, 1), (3, 'PHP', 'PHP is a server scripting language, and a powerful tool for making dynamic and interactive', 2, 1), (4, 'C Language', 'C is a general-purpose, high-level language that was originally developed by Dennis M. Ritchie', 2, 1), (5, 'Frameworks', 'frameworks', 0, 2), (6, 'Laravel', 'Laravel is a PHP web application framework with expressive, elegant syntax, aiming to take the pain out of web development.', 5, 2), (7, 'CodeIgniter', 'CodeIgniter is a powerful PHP framework with a small footprint, built for those who required a simple, yet elegant toolkit', 5, 2), (8, 'CakePHP', 'CakePHP enables you to build web applications faster, using code generation features to rapidly build prototypes.', 5, 2), (9, 'Symfony', 'Symfony is a PHP framework to speed up the creation and maintenance of your web applications.', 5, 2), (10, 'Websites', 'websites', 0, 3), (11, 'Technopoints.co.in', 'Technopoints is blog provides tutorials on php and other programming languages for beginners.', 10, 3), (12, 'Softglobe.net', 'The Web development,Android development and Technology Company in India', 10, 3);
2. Database Configuration
First, to make it convenient, we will create a new PHP file for database configuration named dbconfig.php that holds all configured parameters:
<?php
$con = mysqli_connect("localhost","root","","technopoints");
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
3. Code for Dynamic Dependent Dropdown
In this script we have added two Dynamic Dependent Dropdown one for parent category and another for child category. In the first Select box we select the values from database those are having parent_id=0.
JavaScript’s onChange () function use to get the selected dropdown’s value when users change the selection of drop down.
When user selects a value from first dropdown then onchange function gets called and based on selection value we display value for second drop down.
Based on the selection on the second dynamic dependent dropdown we print the information related to selection value.
Following code shows dynamic dependent dropdown using procedural PHP and MySQLi.
<?php
require 'dbconfig.php';
$pmenu = $cmenu = null;
if (isset($_GET["pcat"]) && is_numeric($_GET["pcat"])) {
$pmenu = $_GET["pcat"];
}
if (isset($_POST['submit'])) {
if (isset($_POST['ccat'])) {
$pmenu = $_POST['pcat'];
}
if (isset($_POST['ccat']) && is_numeric($_POST['ccat'])) {
$cmenu = $_POST['ccat'];
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
<title>Dependent dropdown in PHP, MySQLi</title>
<script type="text/javascript">
function autoSubmit()
{
with (window.document.form) {
if (pcat.selectedIndex === 0) {
window.location.href = 'dependentdropdown.php';
} else {
window.location.href = 'dependentdropdown.php?pcat=' + pcat.options[pcat.selectedIndex].value;
}
}
}
</script>
<link rel="stylesheet" href="dependentdropdown.css">
</head>
<body><center><h2>Dependend Dropdown | <a href="https://technopoints.co.in/">Technopoints</a></h2>
<?php
$actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
?><div class="styled-select slate" style="background-color:rgba(100,200,255,0.5);">
<form class="form" id="form" name="form" method="post" action="<?php echo $actual_link; ?>">
<p class="bg">
<label for="pcat">Select Parent Category</label> <!-- PARENT CATEGORY SELECTION -->
<!--onChange will run autoSubmit()-->
<select id="pcat" name="pcat" onchange="autoSubmit();">
<option value="">-- Select Parent Category --</option>
<?php
//parent_id=0 means main table
$sql = "select category_id,category_name from category where parent_id=0";
$result = mysqli_query($con,$sql);
while ($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
echo ("<option value=\"{$row['category_id']}\" " . ($pmenu == $row['category_id'] ? " selected" : "") . ">{$row['category_name']}</option>");
}
?>
</select>
</p>
<?php
if ($pmenu != '' && is_numeric($pmenu)) {
$sql = "select * from category where parent_id=" . $pmenu;
$result = mysqli_query($con,$sql);
$num = mysqli_num_rows($result);
if ($num > 0) {
?>
<p class="bg">
<label for="ccat">Select Sub-Category</label>
<select id="ccat" name="ccat">
<option value="">-- Select Sub-Category --</option>
<?php
//SUBCATEGORY stared
while ($row = mysqli_fetch_array($result,MYSQLI_ASSOC)) {
echo ("<option value=\"{$row['category_id']}\" " . ($cmenu == $row['category_id'] ? " selected" : "") . ">{$row['category_name']}</option>");
}
?>
</select>
</p>
<?php
}
}
?>
<p><input name="submit" value="Submit" type="submit" /></p>
</form>
<h3><?php
if (isset($_POST['submit'])) {
if (isset($_POST['ccat']) && is_numeric($_POST['ccat'])) {
$qry2 = "SELECT `category_link` FROM `category` WHERE `category_id`=$cmenu";
$result2 = mysqli_query($con,$qry2);
$row2 = mysqli_fetch_array($result2,MYSQLI_ASSOC);
echo implode($row2);
}
}
?></h3>
</div>
<center></body>
</html>
4. CSS for the Relational Select Boxes
Add styling to the Dynamic Dependent by using CSS to make the content pretty and save the file by name dependentdropdown.css
input[type=text] {
width: 95%;
padding: 7px 15px;
margin: 3px 0;
display: inline-block;
border: 1px solid #ccc;
border-radius: 4px;
box-sizing: border-box;
}
input[type=submit]{
padding: 10px 20px;
background-color: white;
color: black;
border: 2px solid #555555;
}
.styled-select.slate select {
border: 1px solid #ccc;
font-size: 16px;
height: 34px;
width: 268px;
}
body {
background-color:rgba(100,200,255,0.5);
}
input[type=submit]:hover {
align:center;
background-color: #555555;
color: white;
}
Now place the two files in the same folder. Remember to save the files with the right extensions (respectively “.htm” and “.css”) . The combined view of the both HTML and CSS is illustrate in the following image.
Finally You have Learned to create Cascaded Select boxes by using procedural PHP and MySQLi.
If you have any doubts regarding to this tutorial feel free to ask in the comment section below.
To catch even more knowledgeable and easy understandable tutorials please subscribe to get email notification.