Load data into dropdown with ajax, php and mysql

Question

Hi I need to populate a dropdown field using a value taken from the database and show it as selected and at the same i would like to show a list of options taken from the database,

Everything works fine except for the field "User Group" this is what i've done so far, can anybody please help me? Many thanks

Html file

<div class="row">
    <div class="col-md-6">
        <div class="form-group">
            <label class="control-label">User Group
                       <span class="required"> * </span>
                      </label>
            <select class="form-control bs-select" id="userGroup" name="userPippo">

                      <?php 

                        $select_group_query="SELECT group_id, group_name FROM user_group";  
                        $run= mysqli_query($conn, $select_group_query);

                        while($row= mysqli_fetch_array($run)) {     

                            echo "<option value= '".$row['group_id']."' >" . $row['group_name'] . "</option>";

                        }

                      ?>  

                      </select>
        </div>
    </div>
</div>

Javascript file

function GetUserDetail(id) {
    $("#EditUserModal").modal("show");
    $("#user_id").val(id);

    var user_id = $('#user_id').val();

    $.ajax({

        url: "../controllers/ctrl_admin_user_app/ctrl_admin_get_user_details.php",
        method: "POST",
        data: {
            user_id: user_id
        },
        dataType: "json",
        success: function(data) {
            console.log(data);
            $('#firstName').val(data.user_first);
            $('#lastName').val(data.user_last);
            $('#userEmail').val(data.user_email);
            $('#userTel').val(data.user_telephone);
            $('#userFiscalcode').val(data.user_fiscalcode);
            $('#userBirth').val(moment(data.user_birth).format('DD/MM/YYYY'));
            $('#userDocument').val(data.user_iddocument);
            $('#userRole').val(data.user_role);
            // ricarico il campo per falo funzionare con il plugin bs-select 
            $('#userRole').selectpicker('refresh');
            $('#userGroup').val(data.group_name); // doesn't work
            // make it work with bs-select 
            $('#userGroup').selectpicker('refresh');
            doesn 't  work

            $("#EditUserModal").modal("show");
        }
    });
}

PHP File

 if (isset($_POST["user_id"])) {
      $userid = $_POST['user_id'];
      $user_id = filter_var($userid, FILTER_SANITIZE_NUMBER_INT);

      $query = "SELECT group_id, group_name, user_first, user_last, user_email, user_telephone, user_fiscalcode, user_birth, user_iddocument, user_role FROM user_group_join LEFT JOIN (user_group, users) ON (user_group_join . group_join_id = user_group . group_id AND user_group_join . user_join_id = users . user_id) WHERE user_join_id = ? ";

      $stmt = mysqli_prepare($conn, $query);
      mysqli_stmt_bind_param($stmt, "i", $user_id);
      mysqli_stmt_execute($stmt);
      $result = mysqli_stmt_get_result($stmt);
      $response = array();
      while ($row = mysqli_fetch_assoc($result)) {
      $response = $row;
  }
  echo json_encode($response);

}

Answer 1

from the Documentation,

.selectpicker('val');

You can set the selected value by calling the val method on the element.

$('.selectpicker').selectpicker('val', 'Mustard');
$('.selectpicker').selectpicker('val', ['Mustard','Relish']);

This is different to calling val() directly on the select element. If you call val() on the element directly, the bootstrap-select ui will not refresh (as the change event only fires from user interaction). You will have to call the ui refresh method yourself.

.selectpicker('refresh');

To programmatically update a select with JavaScript, first manipulate the select, then use the refresh method to update the UI to match the new state. This is necessary when removing or adding options, or when disabling/enabling a select via JavaScript.

$('.selectpicker').selectpicker('refresh');

So,

Replace these lines

$('#userGroup').val(data.group_name); // doesn't work
// make it work with bs-select 
$('#userGroup').selectpicker('refresh');

with this line,

 $('#userGroup').selectpicker('val', data.group_id).selectpicker('refresh');