Populate another select dropdown from database based on dropdown selection

ajax, javascript, php

Solution

I would just make put the variables in javascript with php and then use javascript functions.. no jquery or AJAX needed.

However you need to have a foreign key for subcategories no matter what.. ie - For every record in subcat table you need to give it a catid so for referencing...

<?php
  $db = new mysqli('localhost','user','password','dbname');//set your database handler
  $query = "SELECT id,cat FROM cat";
  $result = $db->query($query);

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

  $query = "SELECT id, catid, subcat FROM subcat";
  $result = $db->query($query);

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

  $jsonCats = json_encode($categories);
  $jsonSubCats = json_encode($subcats);


?>

<!docytpe html>
<html>

  <head>
    <script type='text/javascript'>
      <?php
        echo "var categories = $jsonCats; \n";
        echo "var subcats = $jsonSubCats; \n";
      ?>
      function loadCategories(){
        var select = document.getElementById("categoriesSelect");
        select.onchange = updateSubCats;
        for(var i = 0; i < categories.length; i++){
          select.options[i] = new Option(categories[i].val,categories[i].id);          
        }
      }
      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()'>
    <select id='categoriesSelect'>
    </select>

    <select id='subcatsSelect'>
    </select>
  </body>
</html>

Problem

I am building a website to learn coding and am trying to build a tool where a user clicks on a select/dropdown that contains some category names pulled from database cat and then another select will appear with subcategory names pulled from database subcat. This is almost exactly like Yelp's (go down to the categories) like Yelp's (go down to the categories). I also made a diagram: I already have a category dropdown that is pulling from cat database: ``` <p><b>Category:</b><br /> <?php $query="SELECT id,cat FROM cat"; $result = mysql_query ($query); echo"<select name='cselect3' class='e1'><option value='0'>Please Select A Category</option>"; // printing the list box select command while($catinfo=mysql_fetch_array($result)){//Array or records stored in $nt echo "<option value=\"".htmlspecialchars($catinfo['cat'])."\">".$catinfo['cat']." </option>"; } echo"</select>"; ?> ``` And I have a subcat that is pulling from subcat database: ``` <p><b>Subcat1:</b><br /> <?php $query="SELECT id,subcat FROM subcat"; $result = mysql_query ($query); echo"<select name='sselect1' class='e1'><option value='0'>Please Select A Category</option>"; // printing the list box select command while($catinfo=mysql_fetch_array($result)){//Array or records stored in $nt echo "<option value=\"".htmlspecialchars($catinfo['subcat'])."\">".$catinfo['subcat']."</option>"; } echo"</select>"; ?> ``` How do I make a subcategory dropdown based on what the user clicks on category and make it automatically appear? Thanks so much for any and all help!

Original source