Get JSON data from PHP server-side script and display in dropdown

Question

I have a dropdown box "#pro" Which when selected will send a number to my php script "ft-customerpo.php". This php script should return the entries corresponding with this project number and display the results in another dropdown box named "#custponumhold".

My html:

<label for="txtfield">Project Number <b style="color:red;">*</b></label>
<select id="pro" name="projectnoid"  class="inputvalues" required/>
   <option disabled selected value>-- Project Number</option>
   <?php echo $options_1; ?>
</select><br>

<label>Customer PO Number <b style="color:red;">*</b></label>
<select id="custponumhold" style="width: 250px;" name="custpo" class="inputvalues" required>

</select>

My Javascript:

//CHAINED COMBOBOX
$(document).ready(function() {
  $('#pro').on('change', function() {
    var project = $(this).val();
    if (project) {
      $.ajax({
        type: 'POST',
        url: 'ft-customerpo.php',
        data: 'project=' + project,
        success: function(html) {
          $('#vendponum').html(html);
        }
      });
    }
  });
});

And "ft-customerpo.php"

<?php
require "../inc/dbinfo.inc";

$i=1;
$proj = mysqli_real_escape_string($_POST['projectnoid']);

if($proj)
{
    $rs = $conn->query("SELECT PONumber 
                        FROM tblCustomerPOs 
                        WHERE ProjectNum = '$proj'");

    $options[0] = "<option disabled selected value>-- Customer PO</option>";

   while($row[$i] = $rs->fetch_assoc()) {
        $options .= "<option value='".$row['PONumber']."'>".$row['PONumber']." 
        </option>";
        $i++;
    }
    echo json_encode($options);
}
?>

Currently, when I change the project number it sends the request fine. But I do not get a response back. I think the problem is with my "ft-customerpo.php". Sorry if my logic does not make sense. The Customer PO dropdown (#custponumhold) does not display any options when it should.

Answer 1

Php side you have to do change like this, first you are creating options array but after that you have added it as string

<?php
require "../inc/dbinfo.inc";

$proj = mysqli_real_escape_string($_POST['projectnoid']);

if($proj)
{
    $rs = $conn->query("SELECT PONumber 
                        FROM tblCustomerPOs 
                        WHERE ProjectNum = '$proj'");

    $options = [];
    $options[0] = "<option disabled selected value>-- Customer PO</option>";

    while($row = $rs->fetch_assoc()) {
        $options[] = "<option value='".$row['PONumber']."'>".$row['PONumber']."</option>";
    }
    echo json_encode($options);
}
?>

Javascript side

$(document).ready(function(){
  $('#pro').on('change',function(){
      var project = $(this).val();
      if(project){
          $.ajax({
              type:'POST',
              url:'ft-customerpo.php',
              data: {projectnoid: project },
              dataType:'json',
              success: function(data) {
                  $('#custponumhold').empty();
                  for(let i = 0; i < data.length; i++){
                     $('#custponumhold').append(data[i]);
                  }
              }             
          });
        }
  });
});

---

Answer 2

The while loop does not need to use the $row[$i] and when you do you are not using it later in the loop. $row will be reloaded with the current result row each time round the loop so no array is required.

This loop should suffice

$options = "<option disabled selected value="">-- Customer PO</option>";

while($row = $rs->fetch_assoc()) {
//        ^^ changed here
    $options .= "<option value='".$row['PONumber']."'>".$row['PONumber']." 
    </option>";
}

You can also make your code a little easier to read and therefore maintain by removing some of the concatenation like this

while($row = $rs->fetch_assoc()) {
    $options .= "<option value='$row[PONumber]'>$row[PONumber]</option>";
}

Or like this

while($row = $rs->fetch_assoc()) {
    $options .= "<option value='{$row['PONumber']}'>{$row['PONumber']}</option>";
}

Then you can replace

echo json_encode($options);

with a simple

echo $options;

And it should match what you are doing in the javascript

Answer 3

Why are you generating HTML and then encoding it as JSON and then on the client-side treating it as HTML again (without taking account of the fact you encoded it as JSON)?

Just ditch the json_encode and echo $options instead.

If you're going to return HTML, return HTML. HTML is ready-made markup, JSON is for raw data. Don't conflate the two. The browser can deal with the HTML quite happily without the overhead of placing it into a data-interchange format and then back out again.

I've also made a couple of other necessary adjustments:

PHP:

$i=1;
$proj = mysqli_real_escape_string($_POST['projectnoid']);

if($proj)
{
    $rs = $conn->query("SELECT PONumber 
                        FROM tblCustomerPOs 
                        WHERE ProjectNum = '$proj'");

    $options = "<option disabled selected value>-- Customer PO</option>";

   while($row = $rs->fetch_assoc()) {
        $options .= "<option value='".$row['PONumber']."'>".$row['PONumber']."</option>";
    }
    echo $options;
}
?>

JS:

      $(document).ready(function(){
          $('#pro').on('change',function(){
              var project = $(this).val();
              if(project){
                  $.ajax({
                      type:'POST',
                      url:'ft-customerpo.php',
                      data: { "project": project },
                      success:function(html){
                          $('#custponumhold').html(html);
                      }
                  });
                }
          });
      });