Populate HTML/PHP Dropdown Based on First Dropdown Selection

Question

I have 1 Drop Down Which Is Used For Category (Food, Drink etc.)

In my MySQL Table (t_menu_category) I Have:

+----+---------------+-------------------+----------------------+
| ID | category_name | sub_category_name | category_description |
+----+---------------+-------------------+----------------------+
|  1 | Food          | Curries           | Spicy Curries        |
|  2 | Food          | Meat              | Lamb, Pork, Chicken  |
|  3 | Drinks        | Alcohol           | Fine Tasting Lager   |
|  4 | Desserts      | Cakes             | Chocolate Cake       |
+----+---------------+-------------------+----------------------+

I have got the first dropdown showing the values of "category_name" but what I want is when I select food I want the second dropdown box to update and just show the values of "sub_category_name" where the first selection e.g. "Food" equals "Food" in the database.

So if I selected "Food" in the first dropdown box, the second dropdown box will only show "Curries" & "Meat".

HTML:

<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>"> 
                  
    <p> 
        <label for="item_name">Item Name</label>
        <input id="item_name" name="item_name" required="required" type="text" placeholder="Item Name" />
     </p>
     <p>
        <label for="item_description">Item Description</label>
        <textarea rows="3" cols="100%" required="required" name="item_description">Item Description</textarea>
    </p>
    <p>
        <label for="item_category">Item Category</label>
        <select id="item_category" name="item_category" required="required">
            <option selected="selected">-- Select Category --</option>
            <?php 
            $sql = mysql_query("SELECT category_name FROM t_menu_category");
            while ($row = mysql_fetch_array($sql)){
            ?>
            
            <option value="<?php echo $row['category_name']; ?>"><?php echo $row['category_name']; ?></option>

            <?php
            // close while loop 
            }
            ?>
        </select>
    </p>

    <p class="center"><input class="submit" type="submit" name="submit" value="Add Menu Item"/></p>
</form>

Answer 1

you could create a PHP file with the request and call it with AJAX.

getSubCategory.php

<?php
$category = "";
if(isset($_GET['category'])){
    $category = $_GET['category'];
}

/* Connect to the database, I'm using PDO but you could use mysqli */
$dsn = 'mysql:dbname=my_database;host=127.0.0.1';
$user = 'my_user';
$password = 'my_pass';

try {
    $dbh = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

$sql = 'SELECT sub_category_name as subCategory FROM t_menu_category WHERE category_name = :category';
$stmt = $dbh->prepare($sql);
$stmt->bindValue(':category', $category);
$stmt->execute();

return  json_encode($stmt->fetchAll());

and add some jquery to catch when an category is selected and ask the server for the corresponding sub-category:

<script>
    $(document).ready(function () {
        $('#item_category').on('change', function () {

            //get selected value from category drop down
            var category = $(this).val();

            //select subcategory drop down
            var selectSubCat = $('#item_sub_category');

            if ( category != -1 ) {

                // ask server for sub-categories
                $.getJSON( "getSubCategory.php?category="+category)
                .done(function( result) {    
                    // append each sub-category to second drop down   
                    $.each(result, function(item) {
                        selectSubCat.append($("<option />").val(item.subCategory).text(item.subCategory));
                    });
                    // enable sub-category drop down
                    selectSubCat.prop('disabled', false);                
                });

            } else {                
                // disable sub-category drop down
                selectSubCat.prop('disabled', 'disabled');
            }
        });    

    });
</script>

also add a value on your first option:

<option value="-1" selected="selected">-- Select Category --</option>

Answer 2

I have a simple solution to select states depending on a country php/javascript/mysql

MySQL tables

country 
      country_code varhar(5)
      country_name varchar(100)

state
      country_code varhar(5)
      state_code   varchar(5)
      country_name varchar(100)

Country/State selection in main.php file

<html>
   <body> 
     Country
            <?php
                $sql="SELECT * FROM country order by country_name";
                $rs=$conn->Execute($sql);
                echo '<select  value="'.$country_code.'"  name="country_code"  id="country_list"   onChange="stateList(this.value);" />';
                echo  '<option value="">--Select--</option>';
                $rs->MoveFirst();
                while (!$rs->EOF) {
                    echo  '<option value="'.$rs->fields['country_code'].'"';
                    if  ($rs->fields['country_code'] == $country_code) {echo " selected";}
                    echo  '>'.$rs->fields['country_name'].'</option>';
                    $rs->MoveNext();
                }
                echo '</select>';
            ?>

     State
            <?php
                $sql="SELECT * FROM state where contry_code = '$country_code' order by state_name";
                $rs=$conn->Execute($sql);
                echo '<select   value="'.$state_code.'"  name="state_code" id="state_list"   />';
                echo  '<option value="">--Select--</option>';
                $rs->MoveFirst();
                while (!$rs->EOF) {
                    echo  '<option value="'.$rs->fields['state_code'].'"';
                    if  ($rs->fields['state_code'] == $state_code) {echo " selected";}
                    echo  '>'.$rs->fields['state_name'].'</option>';
                    $rs->MoveNext();
                }
                echo '</select>';
            ?>
   </body>
</html>

Java Script

<script type="text/javascript">
function stateList(val) {
   var select = document.getElementById( "state_list" );
   var url    = "get_statelist.php?country_code="+val;
   $.ajax({
      type: "GET",
      url: url,
      data:'',
      success: function(data){
         $("#state_list").html(data);
      }
   });
}

get_stataelist.php

<?php
session_start();
$country_code = $_GET['country_code'];
$conn        = connect_db()  //Make your own connection entry conn with Server,DB User ID, DB Password and DB Name

if  ($country_code  !=  "") {
    $sql="SELECT * FROM state where coutry_code = '$country_code'  order by state_name";
    $rs=$conn->Execute($sql);

    echo  '<option value="">--Select--</option>';

    $rs->MoveFirst();
    while (!$rs->EOF) {
        echo  '<option value="'.$rs->fields['state_code'].'">'.$rs->fields['state_name']."</option>";
        $rs->MoveNext();
    }
}

?>