Update multiple rows with where clause in codeigniter

Question

I have a list of records from database, see the attachment. How can i insert a vat no for each record using student id(see leftmost column). Each record has a input field(see rightmost column).

Here is my view:

    <?php $attributes = array('id' => 'VATformAdd', 'name' => 

'VATformAdd', 'autocomplete' => 'off'); echo form_open('report/addVATno', $attributes);?>
<fieldset>
<legend>Income By Student Admission</legend>
<table class="">
<thead>
<tr>
<th width="15%">Student ID</th>
<th width="15%">Student Form No</th>
<th width="15%">Course</th>
<th width="20%">Student Name</th>
<th width="15%">Admitted by</th>
<th width="10%">Amount</th>
<th width="10%">VAT Serial No</th>
</tr>
</thead>

<tbody>
<?php
    if (isset($addmission_income)) {
        foreach ($addmission_income as $addmissionincome) {
?>
<tr>
    <td><?php echo $addmissionincome->student_id;?></td>
    <td><?php echo $addmissionincome->student_form_no;?></td>
    <td><?php echo $addmissionincome->course_name;?></td>
    <td><?php echo $addmissionincome->student_full_name;?></td>
    <td><?php echo user_profile_name($addmissionincome->student_added_by); ?></td>
    <td><?php echo $addmissionincome->student_fee_paid;?></td>
    <td><input type="text" name="vatno[<?php echo $addmissionincome->student_id;?>]" class="width-100"/></td>
<?php       
        }
?>
</tr>
<?php       
    }
?>
</tbody>
</table>

<p>
<input id="submit" name="submit" type="submit"  value="Add VAT Number" />
</p>

</fieldset>
<?php echo form_close();?>

Here is my controller:

function addVATno(){
        $studentid = $this->input->post('studentid');
        foreach($studentid as $a){
            if($val[$a]!=''){
                $this->report_mdl->addVATno($a);
            }
        }
    }

Here is Model:

function addVATno($a){
        $val = $this->input->post('vatno');
        $updatevatsl = array( 
            'dupVATserial_no' => $val);
        $this->db->where('student_id', $a);
        $query = $this->db->update('data_student_master', $updatevatsl);
        return $query;
    }

Answer 1

make your view file with a hidden student_id like:

<input type="hidden" name="student_id[]" value="<?php echo $addmissionincome->student_id;?>"/>

and vatno field:

<input type="text" name="vatno[]" class="width-100"/>

Adding the following to your controller will do everything you need (without needing to put any code in your model):

  $student_id = $this->input->post('student_id');
  $vatno = $this->input->post('vatno');

  for ($i = 0; $i < count($student_id); $i++) {
        $sm_data[] = array(
            'vatno' => $vatno[$i],
            'student_id' => $student_id[$i]
        );
  }

$this->db->update_batch('data_student_master', $sm_data, 'student_id');

Note: It would be better to move the db call to your model to adhere to MVC conventions.

Answer 2

Your issue is that you aren't pulling the correct values from POST.

You are not submitting the student id on its own anywhere in the form, so I can't see how you would be able to access it from POST.

You are also trying to access 'vatno' from POST to insert it into a DB, but 'vatno' in POST is an array.

Because you are using the student id as the array key in your form, you can access both the student ID and the vat number you want to update from the same array. In your controller, you can do:

function addVATno(){
    //vatnos is an array, the key is the student id
    $vatnos = $this->input->post('vatno');
    foreach($vatnos as $key=>$vatno){
        $this->report_mdl->addVATno($key, $vatno);
    }
}

Then in your model, you just need:

function addVATno($studentid, $vatno){
    $updatevatsl = array( 
        'dupVATserial_no' => $vatno
    );
    $this->db->where('student_id', $studentid);
    $query = $this->db->update('data_student_master', $updatevatsl);
    return $query;
}