Dependent dropdown example in PHP, MySQL

Introduction

This tutorial shows how to create dependent dropdown in PHP and MYSQL. This is also called cascaded dropdown. This tutorial uses JavaScript to submit the form automatically when the first dropdown value is selected.

Sometimes we need to create such a cascaded dropdown when we need to select some value in the second dropdown based on first dropdown value or third dropdown value based on second dropdown value.

For example, we need to select city dropdown value based on country dropdown value and for this we cannot pre-populate the city dropdown value unless we know the country from country dropdown.

The example I am going to show here contains two dropdowns – one is parent category and another is sub-category. So depending on the parent category the sub-category dropdown gets populated.

Prerequisites

MySQL 8.0.17, PHP 7.4.3, Apache HTTP Server 2.4

MySQL Database Connection Example in PHP

Related Posts:

MySQL Table

We will fetch data into dropdowm from database, so we need to create a table in database.

Notice that the below table has one column called parent_id that determines who is the parent of this category.

CREATE TABLE `category` (
  `category_id` int unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) NOT NULL,
  `category_link` varchar(255) NOT NULL,
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

This kind of table generally holds static data. Therefore we need to populate this table with static data.

These data also help us to test our application.

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,'Java','java',2,1),
(4,'Liferay','liferay',2,1),
(5,'Frameworks','frameworks',0,2),
(6,'JSF','jsf',5,2),
(7,'Struts','struts',5,2),
(8,'Spring','spring',5,2),
(9,'Hibernate','hibernate',5,2),
(10,'Webservices','webservices',0,3),
(11,'REST','rest',10,3),
(12,'SOAP','soap',10,3),
(13,'Contact','contact',0,4),
(14,'About','about',0,5);

While we work with database in php application, we need to establish a connection from php application to database. Then only we can execute database queries from php application.

Project Setup

The project’s root directory name is php-dependent-dropdown created under htdocs folder of Apache HTTP Server.

We may not mention the name of the project’s root directory but whenever we are going to create file or directory, we would assume that we are talking with respect to the project’s root directory.

Dependent Dropdowns

In the below source code we have added two select-option tags for displaying drop down – one for parent and another for child drop down.

In the first drop down we select the values from database those are having parent_id=0.

Notice we have used JavaScript’s onChange() event function 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.

Here is the full source code of the example dependent dropdown in php and mysql. The code is written into dependent-dropdown.php file.

<?php
require_once('config.php');

$pmenu = $cmenu = null;

if (isset($_GET["pcat"]) && is_numeric($_GET["pcat"])) {
    $pmenu = $_GET["pcat"];
}

//when submit button is pressed then parent category id and sub-category id are displayed to the user
if (isset($_POST['submit'])) {
    if (isset($_POST['ccat'])) {
        $pmenu = $_POST['pcat'];
    }
    if (isset($_POST['ccat']) && is_numeric($_POST['ccat'])) {
        $cmenu = $_POST['ccat'];
    }
    if (isset($_POST['ccat']) && is_numeric($_POST['ccat'])) {
        echo 'Parent Cat Id: ' . $pmenu . ' -> ' . 'Subcategory Id: ' . $cmenu;
    } else if (isset($_POST['ccat'])) {
        echo 'Parent Cat Id: ' . $pmenu;
    }
}
?>

<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html;charset=UTF-8" />
        <title>Dependent dropdown in PHP, MySQL</title>
        <script type="text/javascript">
            function autoSubmit() {
                with (window.document.form) {
                    /**
                     * We have if and else block where we check the selected index for parent category(pcat) and * accordingly we change the URL in the browser.
                     */
                    if (pcat.selectedIndex === 0) {
                        window.location.href = 'dependent-dropdown.php';
                    } else {
                        window.location.href = 'dependent-dropdown.php?pcat=' + pcat.options[pcat.selectedIndex].value;
                    }
                }
            }
        </script>
    </head>
    <body>
        <?php
        $actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
        ?>
		<div style="width: 500px; margin: auto;">
			<form class="form" id="form" name="form" method="post" action="<?php echo $actual_link; ?>">
				<fieldset>
					<p class="bg">
						<label for="pcat">Select Parent Category</label> <!-- PARENT CATEGORY SELECTION -->
						<!--onChange event fired and function autoSubmit() is invoked-->
						<select id="pcat" name="pcat" onchange="autoSubmit();">
							<option value="">-- Select Parent Category --</option>
							<?php
							//select parent categories. parent categories are with parent_id=0
							$sql = "select category_id,category_name from category where parent_id=0";
							$result = dbQuery($sql);
							while ($row = dbFetchAssoc($result)) {
								echo ("<option value=\"{$row['category_id']}\" " . ($pmenu == $row['category_id'] ? " selected" : "") . ">{$row['category_name']}</option>");
							}
							?>
						</select>
					</p>
					<?php
					//check whether parent category was really selected and parent category id is numeric
					if ($pmenu != '' && is_numeric($pmenu)) {
						////select sub-categories categories for a given parent category id
						$sql = "select category_id,category_name from category where parent_id=" . $pmenu;
						$result = dbQuery($sql);
						if (dbNumRows($result) > 0) {
							?>
							<p class="bg">
								<label for="ccat">Select Sub-Category</label>
								<select id="ccat" name="ccat">
									<option value="">-- Select Sub-Category --</option>
									<?php
									//POPULATE DROP DOWN WITH SUBCATEGORY FROM A GIVEN PARENT CATEGORY
									while ($row = dbFetchAssoc($result)) {
										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>
				</fieldset>
			</form>
		</div>
    </body>
</html>

Testing the Application

Make sure your web server and MySQL server are running fine.

Now open the dependent-dropdown.php file in the browser to see the results:

dependent dropdown in php and mysql

If your first dropdown does not have any child element then you won’t get the second dropdown.

dependent dropdown in php and mysql

After selecting the category and subcategory, on submission the page you will see the category and child category ids.

Source Code

Download

Thanks for your reading.

Related posts

3 Thoughts to “Dependent dropdown example in PHP, MySQL”

  1. Sakib

    What should be the database name and where is connection code???????????????//

    1. So you didn’t read the article thoroughly.

Leave a Comment