php - 如何使用 codeigniter 使用多个或单个 id 在单个查询中更新多行或单行？

Question

我想在一个查询中更新数据库中的多行或单行，其中多个 ids 或单个 id 从表单发布。我找不到它在更新批处理中有效。我不知道该怎么做。我已经在谷歌上搜索过如何制作它，但我没有运气。非常感谢您的回答。

HTML 和 AJAX

<form class="form-horizontal" enctype="multipart/form-data" method="POST">
   <div class="form-group">
      <label class="col-sm-3 control-label" for="name"><?php echo $this->lang->line('Select Lead Contacts'); ?>
      </label>
      <div class="col-sm-9 col-md-6 col-lg-6">
         <select id="chkveg" name="mySelect[]" class="form-control" multiple="multiple">
            <?php foreach($list_of_leads_contact as $lc) {
               ?>
            <option value="<?php echo $lc->id ?>"><?php echo $lc->first_name ?> <?php echo $lc->last_name ?></option>
            <?php } ?>
         </select>
      </div>
   </div>
   <div class="form-group">
      <label class="col-sm-3 control-label" ><?php echo $this->lang->line('Contact Group'); ?> *
      </label>
      <div class="col-sm-9 col-md-6 col-lg-6">
         <?php if(isset($group_checkbox)) echo $group_checkbox; ?>
         <span class="red">
         <?php
            if($this->session->userdata('group_type_error')==1){
                echo '<b>'.$this->lang->line('Contact Group').'</b>'.str_replace("%s","", $this->lang->line("required"));
            $this->session->unset_userdata('group_type_error');
            }
            ?>
         </span>
      </div>
   </div>
   </div>
   <button name="submit" id="submitButton" type="button" class="btn btn-primary btn-lg"><i class="fa fa-save"></i> <?php echo $this->lang->line('Save');?></button>
</form>
<script type="text/javascript">
   $j(function() {
   $( ".datepicker" ).datepicker();
   });
   
   $(function() {
    $('#submitButton').click(function() {
     var dataString = $('.form-horizontal').serialize();
   
   
     $.ajax({
         type: 'POST',
         url: base_url +'lead/insert_leads_in_groups', // your php url would go here
         data: dataString,
     }).done(function(msg) {
   
     });
   
   
    });
   });
   
   
</script>

Controller 代码

public function insert_leads_in_groups()
{
    $user_id = $this->input->post('mySelect');
    $contact_group_id = $this->input->post('contact_type_id');

    foreach($user_id as $row ) {

        $data = array(
            array(                    
                'contact_group_id' => $contact_group_id,

            ),
        );
        $this->db->update_batch('leads', $data, 'id');
    }

}

错误

A Database Error Occurred
 
One or more rows submitted for batch updating is missing the 
specified index.

Answer 1

在 CI 文档中你可以阅读

the third parameter is the where key.

和下面的结果查询示例。由此可见，每个子数组都应包含该键的对应值。

因此，在您的情况下，您需要将 id 值放入每个子数组。

public function insert_leads_in_groups()
{
    $user_id = $this->input->post('mySelect');
    $contact_group_id = $this->input->post('contact_type_id');
    $data = [];

    foreach($user_id as $id ) {
        $data[] = [
                'id' = > $id,    
                'contact_group_id' => $contact_group_id,
        ];
    }
    $this->db->update_batch('leads', $data, 'id');

}