PHP/Jquery issue populating 3 dynamic select boxes

Question

I am quite new to PHP and JQuery and am struggling with 3 dynamic select boxes. The first one should contain a list of my instruments. The second one a list of categories, and the third should contain a list of subcategories, based on the selected value of the category and selected instrument. Have followed some great tutorials on the matter, but none seem exactly what I need. So far, managed to populate the instruments and the categories select box correctly, but when clicking on the categories select box to select the value I want, it malfunctions, the subcategories box stay empty. I believe the problem is because I do not send the instrumentID correctly when the categories onchange occurs, but cannot seem to find how to properly send it. Can anyone help me please ? This is my code so far :

<?php
    $query = "SELECT * FROM instruments ORDER BY name ASC";
    $result = $db->query($query)->results();
  ?>
<div class = "form-group col-md-3>"
  <select name="instrument_id" id="instrument_id">
    <option value="">-Select Instrument-</option>
    <?php
      foreach($result as $row){
        echo '<option value="'.$row->id.'">'.$row->name.'</option>';
      }
    ?>
</select>
</div>
<div class="form-group col-md-3">
      <label for="category_id" class="control-label">Category</label>
      <select id="category_id" name="category_id" class="form-control input-sm">
        <option value="">-Select Category-</option>
      </select>

</div>
<div class="form-group col-md-3">
  <label for="subcategory_id" class="control-label">Subcategory</label>
  <select id="subcategory_id" name="subcategory_id" class="form-control input-sm">
    <option value="">-Select Subcategory-</option>
  </select>
</div>

    <script>
$(document).ready(function(){
$('#instrument_id').on('change', function(){
    const instrumentID = $(this).val();
    if(instrumentID){
        $.ajax({
            type:'POST',
            url:'<?=PROOT?>admindocuments/get_categories',
            data:{instrument_id: instrumentID},
            success:function(html){
                $('#category_id').html(html);
                $('#subcategory_id').html('<option value="">-Select  Subcategory-</option>');
            }
        });
    }else{
            $('#category_id').html('<option value="">-Select Category-   </option>');
        $('#subcategory_id').html('<option value="">-Select Subcategory-  </option>');
    }
});

$('#category_id').on('change', function(){
    const categoryID = $(this).val();
    const instrumentID = $('#instrument_id').val();
    if(categoryID){
        $.ajax({
            type:'POST',
            url:'<?=PROOT?>admindocuments/get_subcategories',
            data: {
              category_id: categoryID,
              instrument_id: instrumentID,
            },
            success:function(html){
                $('#subcategory_id').html(html);
            }
        });
    }else{
        $('#subcategory_id').html('<option value="">-Select Subcategory-      </option>');
    }
  });
});
</script>

And this is the code in my get_categories.php and get_subcategories.php file :

get_categories :
<?php    
  if($_POST["instrument_id"]){
  $query = "SELECT * FROM categories ORDER BY name ASC";
    $result = $db->query($query)->results();
    echo '<option value="">-Select Category-</option>';
    foreach($result as $row){
    echo '<option value="'.$row->id.'">'.$row->name.'</option>';
  }
}
?>

get_subcategories :

<?php
    if($_POST["category_id"] && !empty($_POST["instrument_id"])){
  $query = "SELECT * FROM subcategories WHERE category_id =  ".$_POST['category_id']." AND instrument_id = ".$_POST['instrument_id']."  ORDER BY name ASC";
      $result = $db->query($query)->results();
    echo '<option value="">-Select Subcategory-</option>';
    foreach($result as $row){
      echo '<option value="'.$row->id.'">'.$row->name.'</option>';
    }
}

What am I doing wrong ? Please help me. Kind regards

Answer 1

The root of your problem is that you aren't sending the instrument id at all when getting subcategories. First you need to fetch it in your handler, since your select has an id, it's easy:

$('#category_id').on('change', function(){
    const categoryID = $(this).val(); // I changed var to const here, read more about it below
    const instrumentID = $('#instrument_id').val(); // this line added

Here you can read about using const and let over var.

And then you need to send it in AJAX data:

// sending it as an object is more readable and PHP reads it just the same
data: {
    category_id: categoryID,
    instrument_id: instrumentID,
}

Now you also need to fix your PHP side. As it currently stands, if you send this kind of data, your first branch would be activated because instrument id is set in both cases:

if (!empty($_POST["instrument_id"])) {
    // get categories
} elseif(!empty($_POST["category_id"])) {
    // get subcategories
}

What you should be checking for instead is if both parameters are set (that's how you determine you need subcategories):

if (!empty($_POST["instrument_id"]) && !empty($_POST["category_id"])) {
    // get subcategories
} elseif (!empty($_POST["category_id"]) && empty($_POST["instrument_id"])) {
    // get categories
}

But a much cleaner solution would be to simply have two PHP scripts, one for categories and one for subcategories. Then you wouldn't need this complex if-elseif structure and your tasks would be logically separated (currently, you're using a file called get_subcategories to get both subcategories and categories, so it isn't really aptly named).

Another thing you should avoid at all costs is building queries by directly inserting parameters:

$query = "SELECT * FROM subcategories WHERE category_id = ".$_POST['category_id']." AND instrument_id = ".$_POST['instrument_id']." ORDER BY name ASC";

This kind of practice leaves you wide open to SQL injection. You should use prepared statements instead.