php, jquery and ajax - populating select dropdown based on previous selected value

Question

I have been told my previous post wasn't very clear, so I am going to try to ask it in a different way.

I am working in an OOP recipe book. At the moment, I I am working on adding recipes. For that, I am displaying a dropdown list of measurements, when the user selects the measurement, I want to display another dropdown with the units found for that measurement_id.

The tables on mysql are measurements, with id and name column, and units, with id, name and measurement_id column (measurement_id is the foreign key for the units table). Multiple units will belong to one measurement.

For that, I have created a measurement php object/class.

On new_recipe.php, I include a form, with the rest of columns to add a recipe, I will include only the matter I am treating now:

NOTE: on measurement.php, the Measurement php object/class has those methods: - display_measurements() --> Just selects ALL measurements from the measurements table. - get_units_for_measurement($measurement_id) --> Selects ALL units with the same measurement_id.

public function display_measurements() {
      include 'includes/db_connection.php';   
          try {

      $sql = "SELECT id, name FROM measurements";

      $results = $conn->prepare($sql);
      $results->execute();
     } catch(PDOException $e) {
        echo 'Error: ' . $e->getMessage() . '<br />';
        return array();
    }
      return $results->fetchAll(PDO::FETCH_ASSOC);
    }


   public function get_units_for_measurement($measurement_id = ':measurement_id') {
       include 'includes/db_connection.php';

       try { 
           $sql = "SELECT measurements.id, units.id, units.name "
               . "FROM measurements "
               . "JOIN units "
               . "ON measurements.id=units.measurement_id WHERE measurements.id=:measurement_id";
           $result = $conn->prepare($sql);
           $result->bindParam(':measurement_id', $measurement_id, PDO::PARAM_INT);
           $result->execute(); 
        } catch(PDOException $e) {
        echo 'Error: ' . $e->getMessage() . '<br />';
        return array();
    }
    return $result->fetchAll(PDO::FETCH_ASSOC);
    }

 <form id="newRecipe" method="POST" action="new_recipe.php">   
(...)
     <div class="input_group">
         <label for="name" class="required">Name</label>
         <input name="name" type="text" value="" />
    </div>
    <div class="input_group">
         <label for="measurements" class="required">Measurements</label>  
         <select name="measurements" class="selectMeasurement">
             <option disabled selected value=""> -- select a measurement -- </option>
<?php
$measurements = $measurementObj->display_measurements();
foreach ($measurements as $measurement) {
    echo '<option value="' . $measurement['id'] . '" ';
    echo '>' . $measurement['name'] . '</option>';
}
?>
       </select>        
   </div>
   <div class="input_group">
       <label for="units" class="required">Units</label>
       <select id="selectUnit" name="units">
           <option disabled selected value=""> -- select a unit -- </option>
             <?php
             if (isset($_POST['measurements'])) {
                 die('ddddd');
                // $units = $measurementObj->get_units_for_measurement($_POST['measurements']);
            //     foreach ($units as $unit) {
            //         echo '<option value="' . $unit['id'] . '" ';
            //         echo '>' . $unit['name'] . '</option>';
            //     }
             }
             ?>
       </select> 
   </div>
   (...)
   <button class="submit" type="submit" name="submit">Submit</button>
</form>

You can see, in the form, two select dropdowns; the first one is the one where I am displaying the measurements, and the next one, that I have with some commented lines, is where I am suposed to "filter" the measurement_id selected in the first dropdown to display the belonging units.To do it not reloading the page, I was advised to do it with ajax (which I am not very familiar with). This ajax call in on a js file, linked to the footer of the project:

$(document).ready(function() {
    $('#newRecipe').on('change','.selectMeasurement', function(){
      var selectedMeasurementId = this.value;
        $.ajax({
            method: 'POST',
            url: 'classes/measurement.php',
            data: selectedMeasurementId
        });
        return true;
    });   
});    

or
$(document).ready(function() { $('#newRecipe').on('change','.selectMeasurement', function(){ var selectedMeasurementId = this.value; $.ajax({ method: 'POST', url: 'classes/measurement.php', data: selectedMeasurementId, success: function() { alert(selectedMeasurementId); } }); });
});

In the js file, what I had in mind was on success, call the method object:

success: $measurementObj->get_units_for_measurement($_POST['measurements']), and I understand, if I have an ajax response, I want to get the units for the given measurement, but, I can't call a PHP object in here.

Also, I tried to do something when the measurements is set. After the select for the measurements I tried to do:

 if (isset($_POST['measurements'])) {
  //then $measurementObbj->get_units_for_measurement|($_POST['measurements']), but it seems it does not "detect" the measurements has been set. 
}

So my question is.... how can I execute this $measurementObj->get_units_for_measurement(measurement_id selected in the first dropdown), for the success of the ajax request?

I hope it is a bit clearer now. Thank you

Answer 1

Disclaimer: this is a just a rough and untested sketch of how you can do it.

As you mentioned OOP, I would first refactor how you access the database to be more object-oriented. I am going to use a shortened version of a DAO pattern. If you follow certain conventions, it makes it very easy to use. There are few drawbacks with this pattern, but for the most part it keeps your code more organized.

First, I would create two classes MeasurementDAO and UnitDAO that will be used for accessing data for each table, respectively. (You could just use one class if it is more convenient for you.)

classes/MeasurementDAO.php

class MeasurementDAO
{
    protected $conn;

    public function __construct(PDO $conn) {
        $this->conn = $conn;
    }

    public function getMeasurements() {
        try {
            // no need to use prepare() if you are not using any placeholders e.g. :name or ?
            // query() and prepare() do not return results, but a (st)atement (h)andler
            $sth = $this->conn->query("SELECT id, name FROM measurements");
            return $sth->fetchAll(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            echo 'Error: ' . $e->getMessage() . '<br />';
        }
    }
}

classes/UnitDAO.php

class UnitDAO
{
    protected $conn;

    public function __construct(PDO $conn) {
        $this->conn = $conn;
    }

    public function getUnitsByMeasurement($measurementId) {
        try { 
            // if I am not mistaken, this query should be shorter and equivalent to your initial one
            $sth = $this->conn->prepare("SELECT * FROM units WHERE measurement_id = ?");
            // I have never seen any reason to use bindParam() as you can just pass your values in execute()
            $sth->execute(array($measurementId));
            return $sth->fetchAll(PDO::FETCH_OBJ);
        } catch (PDOException $e) {
            echo 'Error: ' . $e->getMessage() . '<br />';
        }
    }
}

(You'll probably also want a RecipeDAO.php with methods such as getRecipes(), addRecipe($recipe), updateRecipe($recipe), deleteRecipe($id).)

Next, when you make an AJAX request to a script, you don't want to directly call the class. Instead you want to call a script that uses the class.

ajaxHandler.php

// this will hold the data to be sent back as our response
$res = array();
// initialize connection
$conn = new PDO(/*...*/);
// determine what action to take
if ($_GET['action'] === 'get_units') {
    // get units
    $res['units'] = (new UnitDAO($conn))->getUnitsByMeasurement($_GET['measurement']);
}
header('Content-Type: application/json');
// last line: send response (it should be the only thing that gets outputted)
echo json_encode($res);

You would call the script from your JavaScript by going to e.g. ajaxHandler.php?action=get_units&measurement=123.

Next, we would get the measurements using our new way. (I personally find its better and cleaner to do all the database stuff at the beginning -- opposed to doing it as you are outputting HTML.)

new_recipe.php

<?php
// initialize connection
$conn = new PDO(/*...*/);
// get measurements
$measurements = (new MeasurementDAO($conn))->getMeasurements();
?>

<form id="newRecipe" method="POST" action="new_recipe.php">
    <!-- etc -->
    <div class="input_group">
        <!-- sidenote: label's FOR attribute attaches itself to the inputs's ID attribute, not NAME attribute --> 
        <label for="measurements" class="required">Measurements</label>  
        <select name="measurements" class="selectMeasurement">
            <option disabled selected value=""> -- select a measurement -- </option>
            <?php if ($measurements) : ?>
                <?php foreach ($measurements as $measurement) : ?>
                    <option value="<?php echo $measurement->id ?>"><?php echo $measurement->name ?></option>
                <?php endforeach ?>
            <?php endif ?>
        </select>
    </div>
    <div class="input_group">
        <label for="units" class="required">Units</label>
        <select name="units" id="selectUnit">
            <option disabled selected value=""> -- select a unit -- </option>
        </select> 
    </div>
    <!-- etc -->
</form>

Finally, let's call the ajax handling script and build the list of options.

// shorter syntax for $(document).ready
$(function() {
    $('#newRecipe').on('change', '[name=measurements]', function () {
        console.log('send request with ', this.value);
        // we'll use a $.getJSON() which is a shortened version of $.ajax() 
        // make a GET request to ajaxHandler.php?action=get_units&measurement={this.value}
        $.getJSON('ajaxHandler.php', { action: 'get_units', measurement: this.value }, function (res) {
            console.log('got response', res);
            var html = '';
            // build list of options if any
            if (!$.isEmptyObject(res.units)) {
                $.each(res.units, function (unit) {
                    html += '<option>' + unit.name + '</option>';
                });
            }
            // set list of options
            $('[name=units]').html(html);
        });
    });   
});

That should be it. You'll need to add the missing parts such as including the PHP files.

(If you want to keep your current setup, just follow everything from ajaxHandler.php and make the necessary adjustments.)