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

You have a few things mixed with each other. first what you have in this file: classes/measurement.php? is it the _ measurement class_ or a file which is responsible to get the ajax request and send back the data which ajax asked for? — EhsanT
– EhsanT, Commented Nov 21, 2016 at 19:06
ohm.... it is the _measurement class. Should I rename the file in another way? I was actually looking if it is common to name the file with capital, as the class, but I didn't find anything so I just named with the class name. The file responsible for the ajax request is, at the moment, just on the footer ^^ — eve_mf
– eve_mf, Commented Nov 22, 2016 at 9:20

Community · Accepted Answer · 2017-05-23 12:30:20Z

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.)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related