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.
I will explain here with an example. The example 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 database
PHP engine
Apache HTTP server

At the end of this example you will be able to see this output in the browser for dependent dropdown in php and mysql:

When the dependenddropdown.php file gets executed

Dependent dropdown in php and mysql

When you select the parent category and for this category if there is any sub-category

Dependent dropdown in php and mysql

When you select sub-category and press the submit button

Dependent dropdown in php and mysql

Read also Dependent Dropdown Example

Now look at the below source code for dependent dropdown in php and mysql.

Create MySQL database 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(10) unsigned NOT NULL AUTO_INCREMENT,
  `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',
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `unique` (`category_name`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Insert some data into the table

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

This 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.

Please have a look for database connection tutorial.

Now we will see how it works in dependenddropdown.php file.

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.

<?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">
            /**
             * You can have a look at https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/with * for more information on with() function.
             */
            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 = 'dependenddropdown.php';
                    } else {
                        window.location.href = 'dependenddropdown.php?pcat=' + pcat.options[pcat.selectedIndex].value;
                    }
                }
            }
        </script>
    </head>
    <body>
        <?php
        $actual_link = "http://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
        ?>
        <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>
    </body>
</html>

Thanks for your reading.

Tags:

I am a professional Web developer, Enterprise Application developer, Software Engineer and Blogger. Connect me on JEE Tutorials | TwitterFacebook Google PlusLinkedin | Reddit

2 thoughts on “Dependent dropdown example in PHP, MySQL

Leave a Reply

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