shank shank - 1 year ago 88
MySQL Question

multiple row update only updates last row, Need to update all rows at once

only last row is updating while i am trying to update all the rows using batch update in codeigniter. there are no errors and has been extremely difficult to debug. Any hints what might be the issue as i want to update all rows at once

View code:

<?php global $USER; ?>
padding-left: 10px!important;
padding-right: 10px!important;
.sc_data {
background: #79be17 none repeat scroll 0 0;
border-radius: 3px;
color: #ffffff;
float: left;
font: 13px "proxima_novalight";
padding: 5px 0;
position: absolute;
right: 50px;
bottom: -38px;
<link rel='stylesheet' type='text/css' href='<?php echo base_url(); ?>assets/plugins/fancybox/jquery.fancybox-1.3.4.css' />
<section class="content-header">
Absconding checklist

<section class="content">
<div class="container-fluid">
<div class="row">
<!-- Thought Day-->
<div class="panel wrapper clearfix m-b-none">
<div class="box-header with-border">
<div class="panel-header"><a href="<?php echo base_url(); ?>exits/add_new_absconding_checklist" class="btn btn-info">Add New</a></div>

<div class="box-body">
<?php if($error_message!=''){?>
<?php echo $error_message;?>
<?php } ?>
<form action="news" method="post">
<table border="1" style="background:none;width:100%;" RULES="ROWS" class="tab_data">

<th width="30px">No</th>
<th >Action Item</th>

<th width = "100px" align="center">Actions</th>


foreach($rows as $row){

<tr >

<td ><?php echo $serial_no;?></td>
<td >

<?php echo "<input type='hidden' class='col-md-4 form-control' name='checklist_id' value='".$row['checklist_id']."' />"; ?>

<input type="text" class="form-control" name="action_item" value="<?php echo $row['action_item']; ?>" readonly>

<?php echo $row['responsibility']; ?>
<input type="hidden" name="sequence1" value="<?php echo $row['sequence']; ?>">

<input type="text" class="form-control" name="sequence" id="sequence" value="<?php echo $row['sequence']; ?>">

<input type="checkbox" class="" name="checklist_id<?php echo $row['checklist_id'];?>" value="1" <?php if($row['status'] == '1') echo 'checked'; ?>>


<td align="center">

echo anchor('exits/delete_absconding_checklist/'.$row['checklist_id'],"<i class='fa fa-trash-o' alt='Delete' title='Delete' rel='".$row['id']."' ></i>",array('rel'=>$row->id,'class'=>'edit_row'));


} ?>
<?php ?>
<button type="submit" name="submit" class="btn btn-info pull-right" id="update_button">Update</button>



<script src="<?php echo base_url(); ?>assets/plugins/fancybox/jquery.fancybox-1.3.4.pack.js"/></script>



Controller code:

function display_absconding_checklists()

global $SITE,$USER;
$data = array();
$data['row'] = new stdClass();
$data['row'] = $this->admin_init_elements->set_post_vals($this->input->post());
$data['error_message'] = '';
$data['row']->id = $data['id'] = $this->uri->segment(3);

$data['action'] = 'add';
$data['heading'] = 'Add';
$data['msg_class'] = 'sukses';

$post_action = $this->input->post('action');
if($post_action=='add' || $post_action =='update' ){
$post_array = $this->input->post();
$action = ($post_action == 'add')?'inserted':'updated';
//$data['error_message'] = $this->exit_common->add_edit_attendance_issue($post_array,$action);


$data['rows'] = $this->exit_common->get_all_absconding_checklists();

$data['action'] = 'update';
$data['rows'] = $this->exit_common->get_all_absconding_checklists();

$this->data['maincontent'] = $this->load->view('maincontents/backend_display_absconding_checklist', $data,true);
$this->load->view('layout', $this->data);


function news()



Model code:

function update_absconding_checklist()

$post_array = $this->input->post();
$id = $this->input->post('checklist_id'); //array of id
$action_item = $this->input->post('action_item'); //array of item name
$sequence = $this->input->post('sequence'); //array or qty

$updateArray = array();

for($x = 0; $x < sizeof($id); $x++){

$updateArray[] = array(
'action_item' => $action_item[$x],
'sequence' => $sequence[$x]


$this->db->update_batch('pr_absconding_checklists',$updateArray, 'checklist_id');

Mysql table:

enter image description here

Answer Source

Change the code from view as per the below

<input type="text" class="form-control" name="action_item[<?php echo $row['checklist_id'];?>]" value="<?php echo $row['action_item']; ?>" readonly>
<input type="checkbox" class="" id="checklist_id<?php echo $row['checklist_id'];?>" name="checklist_id[]" value="1" <?php if($row['status'] == '1') echo 'checked'; ?>>
<input type="text" class="form-control" name="sequence[<?php echo $row['checklist_id'];?>]" id="sequence" value="<?php echo $row['sequence']; ?>">

Pass the values in array

In your model

foreach($id as $key => $value) {
   $updateArray[] = array(
        'action_item' => $action_item[$value],
        'sequence' => $sequence[$value]           

use checklist_id as key for action_item, sequence

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download