Updating multiple records in database using Codeigniter

Question

Am trying to update multiple records in the database but my case I have a column total, which I want to update to different values. I haven't tried much here but hope I could get a clue on how to go about this.

My controller

public function update_record()
{
    $id = ["19821", "19923", "19966", "19967"];
    $total = ["8", "118", "90", "100"];

    if ($this->some_model->batch_data('records', $total, $id) == true) {
        echo "yes";
    } else {
        echo "no";
    }

}

The Model

public function batch_data($table, $data, $where)
{
    $this->db->where_in('id',$where); 
    $this->db->set('total',$data); 
    $this->db->update($table);
    return true;
}

I have not tested this yet but currently looking for a more and efficient way of doing this.

you have to use loop to update one by one record with specific id, as per this code all the match record update with total array. — Devsi Odedra
– Devsi Odedra, Commented Jul 24, 2019 at 9:56
yea, i thought as much. but is there any other possible way to do this without looping through them one at a time? — courage
– courage, Commented Jul 24, 2019 at 9:59
@M.Hemant You can't add multiple where clauses to update_batch(). It only accepts a string as the third parameter for the where clause so I'm sure there's no way to do this the way the method is currently written. - stackoverflow.com/questions/7426094/… — splash58
– splash58, Commented Jul 24, 2019 at 10:03

Hasta Dhana · Accepted Answer · 2019-07-24 10:30:16Z

If you still want to update multiple records using the update_batch method, you could first assign the id and total as key-value arrays, then use the update_batch method.

Controller :

public function update_record()
{
    $id = ["19821", "19923", "19966", "19967"];
    $total = ["8", "118", "90", "100"];
    $update_data = [];
    foreach ($id as $key => $value) {
        $update_data[] = [
            'id'    => $value,
            'total' => $total[$key]
        ];
    }

    if ($this->some_model->batch_data('records', $update_data) == true) {
        echo "yes";
    } else {
        echo "no";
    }

}

Model :

public function batch_data($table, $data)
{
    $this->db->update_batch($table, $data, 'id'); // this will set the id column as the condition field
    return true;
}

Output :

// preview query output : 
// UPDATE `records`
// SET
// `total` = 
// CASE 
//     WHEN `id` = '19821' THEN 8 
//     WHEN `id` = '19923' THEN 118 
//     WHEN `id` = '19966' THEN 90 
//     WHEN `id` = '19967' THEN 100 
//     ELSE `total`
// END 
// WHERE `id` IN ('19821', '19923', '19966', '19967')

Alex · Accepted Answer · 2019-07-24 10:29:15Z

0

As per my comment. This is a method that combines the array in to key/value pairs and updates them 1x1 wrapped in a transaction (so if one query fails nothing changes).

This is the method I would personally use as I don't like update_batchs internal workings (cases).

$id = ["19821", "19923", "19966", "19967"];
$total = ["8", "118", "90", "100"];

$combined = array_combine($id, $total);

if (count($combined) > 0) {

    $this->db->trans_start();

    foreach ($combined as $id => $total) {

        $this->db->set('total', $total);
        $this->db->where('id', $id);
        $this->db->update('sometable');

    }

    $this->db->trans_complete();

    return $this->db->trans_status();

}

return true;

answered Jul 24, 2019 at 10:29

Alex

9,2958 gold badges43 silver badges91 bronze badges

Comments

M.Hemant · Accepted Answer · 2019-07-24 11:17:33Z

0

Try this, Only for single where condition

Controller:

public function update_record()
    {
        $tableName = "records";
        $id = ["19821", "19923", "19966", "19967"];
        $total = ["8", "118", "90", "100"];
        $update_data = [];
        foreach ($id as $key => $value) {
            $update_data[] = [
                'id'    => $value,
                'total' => $total[$key]
            ];
        }
        $whereKey = 'id';
        $this->$this->some_model->batch_data($tableName, $updateData, $whereKey);
    }

Model:

public function batch_data($tableName, $updateData, $whereKey)
    {
        $this->db->update_batch($tableName, $updateData, $whereKey);
    }

edited Jul 24, 2019 at 11:17

answered Jul 24, 2019 at 10:24

M.Hemant

2,3551 gold badge11 silver badges14 bronze badges

Collectives™ on Stack Overflow

Updating multiple records in database using Codeigniter

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related