Update MySQL with multi select

Question

I want to update MySQL table with multi select. The option values are in MySQL. My problem is that the multi select show only one company even if the employee has more than 1 company.

For example:

The Employee1 has 3 company, but in the select, it only show 1. And I can't update this. But if an employee has 1 company and I change it then I update it, it works. How can I show in the select all company that the employye has, not just 1?

My table structure:

index.php

<form method="post" id="insert_form">  
   <label>Employee name:</label>  
   <input type="text" name="name" id="name" class="form-control" />  
   <br />  
   <label>Address:</label>  
   <textarea name="address" id="address" class="form-control"></textarea>  
   <br />  
   <label>Company:</label>
   <select name="company" id="company" class="form-control" multiple>
      <?php 
         $query2 = "SELECT * FROM company GROUP BY company_id";  
         $result2 = mysqli_query($connect, $query2);  
         while($row2= mysqli_fetch_array($result2)){
      ?>
      <option value="<?php echo $row2['company_id'];?>"><?php echo $row2['name'];?></option>
      <?php
         }
      ?>
   </select>
   <br/>


   <input type="hidden" name="employee_id" id="employee_id" />  
   <input type="submit" name="insert" id="insert" value="Insert" class="btn btn-success" />  
</form>  

<script>
   $(document).on('click', '.edit_data', function(){  
           var employee_id = $(this).attr("id");  
           $.ajax({  
                url:"fetch.php",  
                method:"POST",  
                data:{'employee_id':employee_id},  
                dataType:"json",  
                success:function(data){  
                     $('#name').val(data.name);   
                     $('#address').val(data.address);
                     $('#company').val(data.company);

                     $('#employee_id').val(data.id);  
                     $('#insert').val("Update");  
                     $('#add_data_Modal').modal('show');  
                }  
           });  
      });  
</script>

fetch.php

if(isset($_POST["employee_id"]))  
 {  
        $query = "SELECT employee.employee_id AS id, employee.name AS name, employee.address AS address, company.company_id AS company
            FROM employee 
            LEFT JOIN employee_company ON employee.employee_id = employee_company.employee_id
            LEFT JOIN company ON company.company_id = employee_company.company_id

            WHERE employee.employee_id = '".$_POST["employee_id"]."'";  
      $result = mysqli_query($connect, $query);  
      $row = mysqli_fetch_array($result);  
      echo json_encode($row);  
 }

Answer 1

You have to cahnge 2 things:

1. Your ajax code - you need to fetch all of the user's company IDs.
2. Your JavaScript code - you need to adjust your code for multiple company values.

I've seperated your ajax code into 2 queries. The first fetches the employee's data, and the second fetches his companies.

if (isset($_POST["employee_id"])) { 
    $return = [];
    $query = "SELECT * FROM employee WHERE employee_id = " . (int)$_POST["employee_id"];
    $result = mysqli_query($connect, $query);

    if ($employee = $result->fetch_assoc()) {
        $companyIdList = [];

        $query = "SELECT company_id FROM employee_company WHERE employee_id = $employee[id]";
        $result = mysqli_query($connect, $query);

        while ($company = $result->fetch_assoc()) {
            $companyIdList[] = $company["company_id"];
        }

        $return = array_merge($employee, ["companies" => $companyIdList]);
    }

    echo json_encode($return);
}

Then replace this line:

$('#company').val(data.company);

With this code:

$.each(data.companies, function(i, e) {
    $("#company option[value='" + e + "']").prop("selected", true);
});

Which will iterate through the array of company IDs, and will select the <option>s accordingly.

Additionaly, right after this line:

$(document).on('click', '.edit_data', function(){

Insert this code, in order to reset the selected <option>s:

$("#company option").prop("selected", false);