使用multi-select更新mysql

llmtgqce  于 2021-06-24  发布在  Mysql
关注(0)|答案(1)|浏览(288)

我想用multi-select更新mysql表。选项值在mysql中。我的问题是,多选只显示一个公司,即使员工有多个公司。
例如:

employee1有3个公司,但在select中只显示1。我不能更新这个。但是如果一个员工有一个公司,我改变了它,然后我更新了它,它就工作了。我怎样才能在“全选公司”中显示员工拥有的,而不仅仅是1个?
我的表结构:

索引.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>

获取.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);  
 }
ukxgm1gy

ukxgm1gy1#

你必须有两件事:
你的ajax代码-你需要获取所有用户的公司id。
你的javascript代码-你需要为多个公司值调整你的代码。
我已经将您的ajax代码分为两个查询。第一个获取雇员的数据,第二个获取他的公司。

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

然后替换此行:

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

使用此代码:

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

它将遍历公司ID数组,并选择 <option> 相应地。
另外,就在这一行之后:

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

插入此代码,以便重置选定的 <option> 学生:

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

相关问题