How to update the exact row in table using codeigniter

Question

My view page:

In this picture i am getting data like loan no, partyname, coll.amt using date (ie it display the data in that particular date when i gave, from "collection" table)

If i have entered the rep amt for loan no 2 but i didn't give to loan no 1, 3. when i pressed ok button the rep amt should be updated in that "collecion" table for exact loan number and date.

My Model code:

public function batchinsert($data){
    $session_data = $this->session->userdata('logged_in');
    $data['username'] = $session_data['repname'];
    $LDate = $this->input->post('CDate');
    $date = str_replace('/', '-', $LDate);
    $newDate = date("Y-m-d", strtotime($date));
    $lno = $this->input->post("Sno");
    $count = count($data['Sno']);
    for($i = 0; $i<$count; $i++){ 
        $entries2[] = array(               
            'receive_amt'=>$data['ramt'][$i],

            ); 
    }
    $this->db->where('loanno',$lno);
    $this->db->where('collection_date',$newDate);
    $this->db->update_batch('collection',$entries2);
    //        $this->db->insert_batch('test', $entries2);
    redirect('Collection_Entry','refresh');
}

My Controller code:

public function Collection_Insert(){
    $this->load->model('User_model');
    $result = $this->User_model->batchinsert($_POST);
}

My View page Code:

<table class="table table-bordered table-striped table-xxs" id="tb3">
    <thead>
        <tr>
            <th>Loan No</th>
            <th>Party Name</th>
            <th>Coll.Amt</th>
            <th>Rep Amt</th>
        </tr>
    </thead>
    <tbody>
    <?php
    //echo '<pre>';print_r($result2);exit();
    if(!empty($query)){
        foreach($query as $row){
    ?>
        <tr >
        <td ><input style="width:50px" type="text" class="form-control input-xs" name="Sno[]" id="Sno" value="<?=$row['loanno'];?>"></td>

        <td> <input style="width:180px" type="text" class="form-control input-xs" name="name[]" id="Amount" value="<?=$row['pname'];?>"></td>

        <td ><input style="width:80px" type="text" class="form-control input-xs amt" name="Amount[]" id="Bankname" value="<?=$row['collection_amt'];?>"></td>

        <td ><input style="width:80px" type="text" class="form-control input-xs ramt" name="ramt[]" id="Chqamt" value="<?=$row['receive_amt'];?>" autofocus></td>
        </tr>

    <?php
        }
    }?> 
    </tbody>
</table>

please solve this problem.

Answer 1

If you want to make updates only on ramt[] input that having non-empty value, you could assign a key to each of the input text name to identify it later :

<table class="table table-bordered table-striped table-xxs" id="tb3">
    <thead>
            <tr>
    <th>Loan No</th>
    <th>Party Name</th>
    <th>Coll.Amt</th>
    <th>Rep Amt</th>
    </tr>
    </thead>
    <tbody>
        <?php
        //echo '<pre>';print_r($result2);exit();
        if(!empty($query)){
            foreach($query as $key => $row){ // added $key as index
            ?>
                <tr >
                    <td ><input style="width:50px" type="text" class="form-control input-xs" name="Sno[<?php echo $key ?>]" id="Sno" value="<?=$row['loanno'];?>"></td>

                    <td> <input style="width:180px" type="text" class="form-control input-xs" name="name[<?php echo $key ?>]" id="Amount" value="<?=$row['pname'];?>"></td>

                    <td ><input style="width:80px" type="text" class="form-control input-xs amt" name="Amount[<?php echo $key ?>]" id="Bankname" value="<?=$row['collection_amt'];?>"></td>

                    <td ><input style="width:80px" type="text" class="form-control input-xs ramt" name="ramt[<?php echo $key ?>]" id="Chqamt" value="<?=$row['receive_amt'];?>" autofocus></td>
                </tr>

            <?php
            }
        }?> 
    </tbody>
</table>

And filter the input data on your batchinsert() function :

public function batchinsert($data){
    $session_data = $this->session->userdata('logged_in');
    $data['username'] = $session_data['repname'];
    $LDate = $this->input->post('CDate');
    $date = str_replace('/', '-', $LDate);
    $newDate = date("Y-m-d", strtotime($date));
    $lno = $this->input->post("Sno");
    $ramt = $this->input->post("ramt"); // added ramt input variable
    $count = count($data['Sno']);
    $updateArray = array();
    for($x = 0; $x < sizeof($lno); $x++){

        if (!empty($ramt[$x])) { // this will only insert data on loanno which have non-empty ramt values
            $updateArray[] = array(
                'loanno' => $lno[$x],
                'receive_amt'=> $ramt[$x]
            );
        }
    }
    $this->db->where('collection_date',$newDate);
    $this->db->update_batch('collection',$updateArray,'loanno');
    //        $this->db->insert_batch('test', $entries2);
    redirect('Collection_Entry','refresh');
}

This will only loops through non-empty ramt[] input then only updates on dynamic loanno values and a static collection_date value.

// Example query output : 
// UPDATE `collection`
// SET
// `receive_amt` = 
// CASE 
//     WHEN `loanno` = '1' THEN 1 
//     WHEN `loanno` = '3' THEN 2 
//     WHEN `loanno` = '6' THEN 3 
//     WHEN `loanno` = '17' THEN 4 
//     ELSE `receive_amt`
// END 
// WHERE `collection_date` = '2019/01/09' AND `loanno` IN ('1','3','6','17')