Populate another select dropdown from database based on dropdown selection

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:

enter image description here

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!

Answers:

Answer

Since the data in your Sub-Category drop down is dependent on what is selected in the category, you probably want to use ajax. You can set an event listener on your category drop down and when it changes you can request the data for the subcategory drop down and populate it, there are many different ways to go about it, below is one option (using jquery) to get you started.

// warning sub optimal jquery code 
$(function(){

   // listen to events on the category dropdown
   $('#cat').change(function(){

       // don't do anything if use selects "Select Cat"
       if($(this).val() !== "Select Cat") {

           // subcat.php would return the list of option elements 
           // based on the category provided, if you have spaces in 
           // your values you will need to escape the values
           $.get('subcat.php?cat='+ $(this).val(), function(result){
               $('#subcat').html(result);
           });

       }

   });

});
Answer

make this html structure on landing page

<p><b>Category:</b><br />
 <?php
  $query="SELECT id,cat FROM cat";
  $result = mysql_query ($query);
  echo"<select name='cselect3' onChange='loadSubCats(this.value)' 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>";
?>

<div id='sub_categories'></div>

make a js function assigned to the category dropdown

function loadSubCats(value)
{
  $.post('load_sub_cats.php',{catid : value},function{data}
                                             {
                                              $('#sub_categories').html(data);

                                             });

}

now in your load_sub_cats.php

<p><b>Subcat1:</b><br />
<?php
  $catid = $_POST['cat_id']
  $query="SELECT id,subcat FROM subcat where catid = $catid";
  $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>";
?>

You will need to include jquery to this code work.

Answer

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>
Answer

If you are using AJAX, you will want that second bit of code to be a separate php file which you will call via AJAX. in the callback from the AJAX call, just do (pseudo-code): someContainingDivOrSomething.innerHtml = responseBody;.

Note that it's generally a bad idea to do querying within your PHP display files directly (separation of concerns). There are several other things that could be improved. However, this will get you started.

Tags

Recent Questions

Top Questions

Home Tags Terms of Service Privacy Policy DMCA Contact Us

©2020 All rights reserved.