使用多个数据更新MySQL

Question

I want to update tables with multi select. The multi select has MySQL data. It's working if an Employee has 1 company, but if it has 2 or more, it didn't update it.

I display the current companies in the select menu. So the Employee#1 has 2 company, but it didn't update it if I select another 2. How can I solve this? The employee_company is my related table.

Multi select

<select size="10" 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>

Update

$name = mysqli_real_escape_string($connect, $_POST["name"]);  
$address = mysqli_real_escape_string($connect, $_POST["address"]); 
$company = mysqli_real_escape_string($connect, $_POST["company"]);

$id = $_POST["employee_id"];

if($id != ''){             
    foreach($_POST['company'] as $comp){
        $query = "
        UPDATE      employee t1
        LEFT JOIN   employee_company t2 ON (t1.employee_id = t2.employee_id)            
        SET         t1.name='$name',
                    t1.address='$address',
                    t2.employee_id='$id',
                    t2.company_id='$comp'            
        WHERE       t1.employee_id='$id'"; 
    }               
       $message = 'Data Updated';            
  }  
  else{  
  }  
  if(mysqli_query($connect, $query)){  
       $output .= '<label class="text-success">' . $message . '</label>';  
  }  
  echo $output;

Answer 1

company[] is an array - you have to IMPLODE values if you want to have them as a string.

 $company = mysqli_real_escape_string($connect, implode(",",$_POST["company"]));

My advice is to revise the data model and normalize it - do not put company IDs as a string in column company_id and instead use a table employee_companies to hold the associations between an employee and zero/one/many companies.

UPDATE

What I meant was something like this

"START TRANSACTION"
"UPDATE employee SET name = '$name', address = '$address' WHERE employee_id = $id"
"DELETE FROM employee_company WHERE employee_id = $id"

if(is_array($_POST["company"])) 
{
  $values = Array();
  foreach($_POST["company"] as $c_id) $values[] = "($id, $c_id)";

  "INSERT INTO employee_company(employee_id, company_id) VALUES ".implode(",", $values)
}
"COMMIT"