shank shank - 3 months ago 12
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; ?>
<style>
.dataTables_filter
{
padding:10px;
}
.caption
{
margin-left:10px;
}
.dataTables_paginate
{
float:right;
margin-right:10px;
}
td,th{
padding-left: 10px!important;
padding-right: 10px!important;
}
.dashboard-stat
{
padding:0px!important;;
}
.add_area3{
display:none;
}
.sc_data {
background: #79be17 none repeat scroll 0 0;
border-radius: 3px;
color: #ffffff;
float: left;
font: 13px "proxima_novalight";
padding: 5px 0;
}
#update_button
{
position: absolute;
right: 50px;
bottom: -38px;
}
</style>
<link rel='stylesheet' type='text/css' href='<?php echo base_url(); ?>assets/plugins/fancybox/jquery.fancybox-1.3.4.css' />
<section class="content-header">
<h1>
Absconding checklist
</h1>

</section>
<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>
<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">
<thead>

<th width="30px">No</th>
<th >Action Item</th>
<th>Responsibility</th>
<th>Order</th>
<th>Mandatory?</th>

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

<?php
$serial_no=1;

if(count($rows)){
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>



</td>
<td>
<?php echo $row['responsibility']; ?>
</td>
<td>
<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']; ?>">

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



</td>


<td align="center">

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

</td>
</tr>
<?php
$serial_no++;

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

</tbody>

</table>
</form>
</div>
</div>
</div>
</div>

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




$('#add_hiring_fields').click(function(){
$('.add_area3').show();
});

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


$data['path']=$path;
$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);


}
if($data['id']>0){

$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()
{


$this->exit_common->update_absconding_checklist();

}





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(
'checklist_id'=>$id[$x],
'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

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(
        'checklist_id'=>$value,
        'action_item' => $action_item[$value],
        'sequence' => $sequence[$value]           
    );
}

use checklist_id as key for action_item, sequence

Comments