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);  
 }

`SELECT employee.id` ... and later, in the same query : `employee.employee_id` wrong copy paste ? — Cid, Jun 29 '18 at 08:46
By the way, your query is vulnerable to **SQL Injections** This is another topic but you should take a look at php PDO and the bind of values — Cid, Jun 29 '18 at 08:48
I edit my post with the table structure. And yes, wrong copy paste. — Derex, Jun 29 '18 at 08:50

HTMHell · Accepted Answer · 2018-06-29T10:09:06.450

You have to cahnge 2 things:

Your ajax code - you need to fetch all of the user's company IDs.
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);

Nothing happen if I click on the edit button to see the output — Derex, Jun 29 '18 at 09:50
Does the ajax call is being fired? Do you have errors in your console? if you don't know how to monitor ajax calls, take a look here: https://stackoverflow.com/questions/1820927/request-monitoring-in-chrome — HTMHell, Jun 29 '18 at 09:51
I forgot to definied the result. It's actually working, but if I select an employee with 2 company, then I select a new one with no company, the 2 selects remain. — Derex, Jun 29 '18 at 10:05

Update MySQL with multi select

index.php

fetch.php

1 Answers1