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 you may need to create such a cascaded dropdown when you 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, you need to select city dropdown value based on country dropdown value and for this you cannot pre-populate the city dropdown value unless you 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 – 8.0.22, PHP 7.4.3, Apache HTTP Server 2.4

MySQL Database Connection Example in PHP

Related Posts:

MySQL Table

I will fetch data into dropdown from database, so I 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 COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `category_name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category_link` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  `sort_order` int COLLATE utf8mb4_unicode_ci 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 I need to populate this table with static data.

These data also help you to test the 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 you want to work with database in php application, you need to establish a connection from php application to database. Then only you 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.

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

Dependent Dropdowns

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

In the first drop down I selected the values from database those are having parent_id=0.

Notice I 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 I 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) {
                    /**
                     * I have if and else block where I check the selected index for parent category(pcat) and * accordingly I 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

3 thoughts on “Dependent Dropdown Example in PHP, MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *