在成功的ajax上，使用预选的选项值从另一个表创建一个下拉列表选择

Question

For an ‘Edit’ modal, I initiate an ajax call to the php script named getSelectedMember.php that bring the information from the table items (Table 2).

<?php 
   require_once 'db_connect.php';  

   $memberId = $_POST['member_id']; 

   $sql = "SELECT * FROM items WHERE itemID = $memberId";
   $query = $connect->query($sql);
   $result = $query->fetch_assoc();

   echo json_encode($result);
?>

This formulation_fk is in the table items a value of a select option from another table named formulation (Table 1).

This is code of 'edit.php' :

<form action=" " method="POST">
   <div>
      <label>Name</label>
      <input type="text"><br>
   </div>
   <div>
      <label>Formulation</label>
      <select id="editFormulation">
      </select>
   </div>
   <button type = "submit">Save changes</button>
</form>

My question is while updating a single item, how can I pass the select options from the formulation table in my edit form where the select option value will be the formulation_fk from the table items?

And this is my ajax call:

$.ajax({
  url: 'getSelectedMember.php',
  type: 'post',
  data: {
    member_id: itemID
  },
  dataType: 'json',
  success: function(response) {
    $("#editName").val(response.name);
    $("#editFormulation").val(response.formulation_fk);
    $(".editMemberModal").append( ? ? ? ? ? ? )
  }
});

For clarification of my question, let's think that to edit Water, the action flow would be like this:

1. Click the edit button for ‘Water’.
2. Ajax call to getSelectedMember.php to get the name (Water) and formulation_fk (1).
3. On response, Name field will output ‘Water’ and Formulation filed will output a dropdown select from formulation table where option value = “1”

Something like this image below.

I have been trying to solve it for a while but I'll really appreciate any suggestion or expert help. Thanks in advance.

Answer 1

The PHP code needs to return an array of all the formulations, in addition to the formulation_fk from the items table.

$memberId = $_POST['member_id']; 

$sql = "SELECT * FROM items WHERE itemID = $memberId";
$query = $connect->query($sql);
$result = $query->fetch_assoc();

$sql = "SELECT * FROM formulation";
$query = $connect->query($sql);
$formulations = array();
while ($row = $query->fetch_assoc()) {
    $formulations[] = $row;
}
$result['formulations'] = $formulations;

echo json_encode($result);

Then the AJAX code can fill in the <select> before setting its value.

success: function(response) {
    $("#editFormulation").empty(); // Clear out previous value
    $.each(response.formulations, function() {
        $("#editFormulation").append($("<option>", {
            value: this.formulationID,
            text: this.formulation_name
        }));
    });
    $("#editFormulation").val(response.valuation_fk);
    $("#editName").val(response.name);
}