Specs Specs - 1 month ago 10
MySQL Question

Populate select box from database query based on another select box using only 1 table

Hi guys I am currently creating a website with the main function of booking cars. Therefore the user would be first able to select a car category from the database query which will output the selected car category names. After selecting the car category name, the second select box would then by dynamically updated with a new set of of car names that are associated with the category.

There may have been many similar questions to this however for the website NO jQuery or AJAX can be utilized. And other questions and examples often utilize 2 or more tables however I am using only 1 table for this function.

The example below should show how the Select box should be updated:
Dynamically updated select box.

The table utilized is this:
Table Utilized

My codes are as such:



<?php
session_start();
include "dbconn.php";


$query = "SELECT carid, brand FROM cars";
$result = $dbcnx->query($query);

while($row = $result->fetch_assoc()){
$subcats[] = array("id" => $row['carid'], "val" => $row['brand']);
}


$jsonSubCats = json_encode($subcats);


?>

<!docytpe html>
<html>

<head>
<script type='text/javascript'>
<?php

echo "var subcats = $jsonSubCats; \n";
?>
function loadCategories(){
var select = document.getElementById("categoriesSelect");
select.onchange = updateSubCats;
}
function updateSubCats(){
var catSelect = this;
var catid = this.value;
var subcatSelect = document.getElementById("subcatsSelect");
subcatSelect.options.length = 0; //delete all options if any present
for(var i = 0; i < subcats[catid].length; i++){
subcatSelect.options[i] = new Option(subcats[catid][i].val,subcats[catid][i].id);
}
}
</script>

</head>

<body onload='loadCategories()'>
Car Category:<br />
<select size="1" name="categoriesSelect" id="categoriesSelect">
<option> Select Car Category </option>
<option value="sedan"> Sedan </option>
<option value="mpv"> MPV </option>
<option value="hatch"> Hatchback </option>
</select><br /><br />

Car Brand:<br />
<select id='subcatsSelect'>
</select>
</body>
</html>





I may have identified the error to the php statement:

while($row = $result->fetch_assoc()){
$subcats[] = array("id" => $row['carid'], "val" => $row['brand']);


As I can only see the first select box query correctly consisting of the 3 car category of MPV, Sedan and Hatchback.

Currently the all the Car Brand name appears however the results do not correspond with the car category as shown in the database.

Thank You.

Answer

As both arrays in PHP follow the same pattern when created the json data will be similar also thus the method to traverse the data should follow that in loadCategories function - so perhaps this:

  function updateSubCats(){
    var oSelect = document.getElementById("subcatsSelect");
    oSelect.options.length = 0;

    for( var i in subcats ){
        oSelect.options[ i ]=new Option( subcats[ i ].val, subcats[ i ].id );
    }
  }

Without using Ajax or over complicating things with complex json iteration you could try along these lines. It should be noted that embedding the $_GET variable directly in the sql is not the best option - prepared statements would be better but should give the idea.

<?php
    session_start();
    include "dbconn.php";
?>

<!docytpe html>
<html>
  <head>
    <title>Chained select</title>
    <script type='text/javascript'>

        function bindEvents(){
            document.getElementById('categories').addEventListener( 'change', getSubcategories, false );
        }

        function getSubcategories(e){
            var el=e.target ? e.target : e.srcElement;
            var value=el.options[ el.options.selectedIndex ].value;
            location.search='?category='+value;
        }

        document.addEventListener( 'DOMContentLoaded', bindEvents, false );
    </script>
  </head>

  <body>
    <form>
        <select id='categories'>
        <?php

            $query = "SELECT carcat FROM cars";
            $result = $dbcnx->query($query);

            while( $row = $result->fetch_assoc() ){
                echo "<option value='{$cat['carcat']}'>{$cat['carcat']}";
            }

        ?>
        </select>
        <select id='subcategories'>
            <?php

                if( !empty( $_GET['category'] ) ){

                    $sql = "SELECT carid, brand FROM cars where carcat='{$_GET['category']}';";
                    $result = $dbcnx->query( $sql );

                    if( $result ){
                        while( $row = $result->fetch_assoc() ){
                            echo "<option value='{$row['carid']}'>{$row['brand']}";
                        }
                    }
                }

            ?>
        </select>
    </form>
  </body>
</html>