Dependent drop down with multiple .onchange

Question

I am working on a search system where the user is prompted with 4 drop downs on the index.php. I have that the user selects the first drop down and then the values of the 2nd, 3rd and 4th drop down values changes to the value that is relevant to the first drop down. I am using the _POST to get the onchange value and to display the data to the rest of drop down. I have also done the same that if the user selects the 2nd drop down on the initial search the 1st ,3rd and 4th values changes to the values that are relevant to the selected drop down. I have done this for all the drop downs. The system is working, but is there a better way of doing this rather than having multiple _POST and .onchange values?

This is the script for the drop down:

      $(document).ready(function(){

/////start commodity post
$('#pfcommodity').on('change', function(){
var pfcommodity =  $(this).val();
 if(pfcommodity){

$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfcategory",
 data:'pfcommodity='+pfcommodity,
success: function(data)
    {
       //console.log(data);
          $("#pfcategory").html(data);       
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pforigin",
 data:'pfcommodity='+pfcommodity,
success: function(data)
    {       
        $("#pforigin").html(data);        
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfmanufacture",
 data:'pfcommodity='+pfcommodity,
success: function(data)
    {       
        $("#pfmanufacture").html(data);        
    }
});

}
});////close commodity post


////open category post
$('#pfcategory').on('change', function(){
var pfcategory =  $(this).val();
 if(pfcategory){

$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfcategory",
 data:'pfcategory='+pfcategory,
success: function(data)
    {
       //console.log(data);
          $("#pfcommodity").html(data);       
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pforigin",
 data:'pfcategory='+pfcategory,
success: function(data)
    {       
        $("#pforigin").html(data);        
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfmanufacture",
 data:'pfcategory='+pfcategory,
success: function(data)
    {       
        $("#pfmanufacture").html(data);        
    }
});



}
});////close category post

// ////open origin post
$('#pforigin').on('change', function(){
var pforigin =  $(this).val();
 if(pforigin){

$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfcategory",
 data:'pforigin='+pforigin,
success: function(data)
    {
       //console.log(data);
          $("#pfcommodity").html(data);       
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pforigin",
 data:'pforigin='+pforigin,
success: function(data)
    {       
        $("#pfcategory").html(data);        
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfmanufacture",
 data:'pforigin='+pforigin,
success: function(data)
    {       
        $("#pfmanufacture").html(data);        
    }
});


}
});////close origin post

// ////open manufacure post
$('#pfmanufacture').on('change', function(){
var pfmanufacture =  $(this).val();
 if(pfmanufacture){

$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfcategory",
 data:'pfmanufacture='+pfmanufacture,
success: function(data)
    {
       //console.log(data);
          $("#pfcommodity").html(data);       
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pforigin",
 data:'pfmanufacture='+pfmanufacture,
success: function(data)
    {       
        $("#pfcategory").html(data);        
    }
});
$.ajax({
method: "POST",
url: "/import-conditions/processfood/pfmanufacture",
 data:'pfmanufacture='+pfmanufacture,
success: function(data)
    {       
        $("#pforigin").html(data);        
    }
});


}
});////close manufacture post



});

This is the first query pfcategory.php

<?php
    if (isset($_POST['pfcommodity'])){  

    $connect=mysqli_connect("localhost","root","","import-conditions");
    $output='';

    $sql="SELECT DISTINCT category FROM processfood WHERE commodity = '".$_POST["pfcommodity"]."'";
    $result=mysqli_query($connect,$sql);
     $output.='<option value="">Select Category</option>';



    while ($row=mysqli_fetch_array($result)) {
        $output.='<option value="">'.$row["category"].'</option>';   

      }

    echo $output;

        }

        if (isset($_POST['pfcategory'])){   

    $connect=mysqli_connect("localhost","root","","import-conditions");
    $output='';

    $sql="SELECT DISTINCT commodity FROM processfood WHERE category = '".$_POST["pfcategory"]."'";
    $result=mysqli_query($connect,$sql);
     $output.='<option value="">Select Commodity</option>';



    while ($row=mysqli_fetch_array($result)) {
        $output.='<option value="">'.$row["commodity"].'</option>';   

      }

    echo $output;

        }

        if (isset($_POST['pforigin'])){ 

    $connect=mysqli_connect("localhost","root","","import-conditions");
    $output='';

    $sql="SELECT DISTINCT commodity FROM processfood WHERE origin = '".$_POST["pforigin"]."'";
    $result=mysqli_query($connect,$sql);
     $output.='<option value="">Select Commodity</option>';



    while ($row=mysqli_fetch_array($result)) {
        $output.='<option value="">'.$row["commodity"].'</option>';   

      }

    echo $output;

        }

        if (isset($_POST['pfmanufacture'])){    

    $connect=mysqli_connect("localhost","root","","import-conditions");
    $output='';

    $sql="SELECT DISTINCT commodity FROM processfood WHERE manufacture = '".$_POST["pfmanufacture"]."'";
    $result=mysqli_query($connect,$sql);
     $output.='<option value="">Select Commodity</option>';



    while ($row=mysqli_fetch_array($result)) {
        $output.='<option value="">'.$row["commodity"].'</option>';   

      }

    echo $output;

        }

        ?>

this is pforigin.php

<?php
$connect=mysqli_connect("localhost","root","","import-conditions");
$output='';

if (isset($_POST['pfcommodity'])){  
$sql="SELECT DISTINCT origin FROM processfood WHERE commodity = '".$_POST["pfcommodity"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Country of Origin</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["origin"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pfcategory'])){   

$sql="SELECT DISTINCT origin FROM processfood WHERE category = '".$_POST["pfcategory"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Country of Origin</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["origin"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pforigin'])){ 

$sql="SELECT DISTINCT category FROM processfood WHERE origin = '".$_POST["pforigin"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Category</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["category"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pfmanufacture'])){    

$sql="SELECT DISTINCT category FROM processfood WHERE manufacture = '".$_POST["pfmanufacture"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Category</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["category"].'</option>';   

  }

echo $output;

    }

    ?>

this is pfmanufacture.php

<?php
$connect=mysqli_connect("localhost","root","","import-conditions");
$output='';

if (isset($_POST['pfcommodity'])){  

$sql="SELECT DISTINCT manufacture FROM processfood WHERE commodity = '".$_POST["pfcommodity"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Manufacture</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["manufacture"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pfcategory'])){   

$sql="SELECT DISTINCT manufacture FROM processfood WHERE category = '".$_POST["pfcategory"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Manufacture</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["manufacture"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pforigin'])){ 

$sql="SELECT DISTINCT manufacture FROM processfood WHERE origin = '".$_POST["pforigin"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Manufacture</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["manufacture"].'</option>';   

  }

echo $output;

    }

    if (isset($_POST['pfmanufacture'])){    

$sql="SELECT DISTINCT origin FROM processfood WHERE manufacture = '".$_POST["pfmanufacture"]."'";
$result=mysqli_query($connect,$sql);
 $output.='<option value="">Select Country of Origin</option>';
while ($row=mysqli_fetch_array($result)) {
    $output.='<option value="">'.$row["origin"].'</option>';   

  }

echo $output;

    }

    ?>

All of the other pages follow this same pattern. There is no problem with it so far, but is there a better way of doing this?

DRY.. if its used more then once function'ize it. also organise the php, so much repeating, even connection which is not needed. — Lawrence Cherone, Aug 04 '18 at 14:10
Lawrence Cherone I will organize it. I have just finished on the code but if there is a better way of handling all of the post i would change it. — user5469526, Aug 04 '18 at 14:13
[How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php).. with the code sure, you could reduce that code by 75% by looking whats different between them (array keys, columns names) then wrap a single one in a function and pass in the differences as params. If the code works but you just want to know how it could be improved, you should ask on https://codereview.stackexchange.com instead. — Lawrence Cherone, Aug 04 '18 at 14:21
I have never used function in a php. Please elaborate. If i could reduce the code by 75% i would be better. I have used function in C# not in php. — user5469526, Aug 04 '18 at 14:23

Lawrence Cherone · Accepted Answer · 2018-08-04T14:55:44.870

First reduce the jquery code by using a class for the event handler, use a data-type attribute to pass in the type.

<select class="pf_select" data-type="commodity">
    <option value="">Select Category</option>
    <option value="a">A</option>
    <option value="b">B</option>
    <option value="c">C</option>
<select>

Doing so you can remove all but one of them jquery calls, with a small change.

$('.pf_select').on('change', function() {
    var elm = $(this);
    var type = elm.data('type');
    var commodity = elm.val();

    if (commodity !== '') {

        $.ajax({
            method: "POST",
            url: "/import-conditions/processfood/pfcategory",
            data: 'pf' + type + '=' + commodity,
            success: function(data) {
                elm.html(data);
            }
        });
        $.ajax({
            method: "POST",
            url: "/import-conditions/processfood/pforigin",
            data: 'pf' + type + '=' + commodity,
            success: function(data) {
                elm.html(data);
            }
        });
        $.ajax({
            method: "POST",
            url: "/import-conditions/processfood/pfmanufacture",
            data: 'pf' + type + '=' + commodity,
            success: function(data) {
                elm.html(data);
            }
        });
    }
});

Now onto the PHP:

You only need to connect to the database once, so just put that at the top.

Then all that changes between the calls, is the table name and the POST variable key. So you can easily wrap the database call and the html render, into a single function and pass in variables.

Then you simply call the function inside your if statement.

<?php
$connect = mysqli_connect("localhost","root","","import-conditions");

function pf_select($key, $select_label = 'Select Category') {
    global $connect;

    $stmt = $connect->prepare('
        SELECT DISTINCT category 
        FROM processfood 
        WHERE '.$key.' = ?');

    $stmt->bind_param('s', $_POST['pf'.$key]);

    $stmt->execute();

    $result = $stmt->get_result();

    $output = '<option value="">'.$select_label.'</option>';
    while ($row = $result->fetch_assoc()) {
        $output .= '<option value="'.$row["category"].'">'.$row["category"].'</option>';   
    }

    return $output;
}

if (isset($_POST['pfcommodity'])) { 
    exit(pf_select('commodity', 'Select Category'));
}

if (isset($_POST['pfcategory'])) { 
    exit(pf_select('category', 'Select Commodity'));
}

if (isset($_POST['pforigin'])) { 
    exit(pf_select('origin', 'Select Commodity'));
}

if (isset($_POST['pfmanufacture'])) { 
    exit(pf_select('manufacture', 'Select Commodity'));
}

Much more readable, and manageable.

Note: I did not add database error checking, you should add that anyway, and also read up on preventing SQL injection.

This is far better than what i had. Your a genius. This is a prepare statement? — user5469526, Aug 04 '18 at 15:02
np, thanks for accept, yeah its a prepared query, as you can its very similar to what you already had but much safer (as long as $key in `'.$key.' = ?` is hardcoded and not user supplied). — Lawrence Cherone, Aug 04 '18 at 15:04

Dependent drop down with multiple .onchange

1 Answers1