How to update multiple or single row in single query using where multiple or single id using codeigniter?

I want to update multiple or single row in database in one query where multiple ids or single id post from the form. I couldn’t find it works in update batch. I don’t know how to do it. I have already searched on google on how to make it but I have no luck. Your answer is very much appreciated.

Html and 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 Code

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

}

Error

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

Answer

In CI docs you can read

the third parameter is the where key.

and below see the resulting query example. From that its’t obviouse that each sub-array should contain a corresponding value for that key.

So, in your case, you need to put the id value to each sub-array.

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

}

Advertisement

Answer