Codeigniter active record - update query using data from array

Question

I have an array which itself contains two arrays - one holds item ids and the other holds the order of the item

They look something like this:

    Array
(
    [item_order] => Array
        (
            [0] => 0
            [1] => 1
            [2] => 2
        )

    [item_id] => Array
        (
            [0] => 20
            [1] => 34
            [2] => 24
        )

)

What I now need to do is update each items row order based on its id

Not sure where to start with this one though.

The end result should look something like

item_id     item_name     item_order
------------------------------------
20          xxxxx         0
34          yyyyy         1
24          zzzzz         2

baldrs · Accepted Answer · 2014-02-26 22:05:20Z

1

You can transform data into an array of arrays, containing key => value pairs(where keys are field names) and use update_batch method of codeigniters active_record class:

list($item_id, $item_order) = $array_with_data; // or extract($array_with_data);

$data = [];

foreach($item_id as $i => $id) {
 $data[] = [ 'item_id' => $id, 'item_order' => $item_order[$i], ];
}

$this->db->update_batch('yourtable', $data, 'item_id');

What is going on in foreach loop

This:

Array
(
   [item_order] => Array
      (
        [0] => 0
        [1] => 1
        [2] => 2
      )

    [item_id] => Array
      (
        [0] => 20
        [1] => 34
        [2] => 24
      )

)

Is turned into this

Array
( 
  [0] => Array
  (
    'item_order' => 0
    'item_id' => 20
  )
  [1] => Array
  (
    'item_order' => 1
    'item_id' => 34
  )
  [2] => Array
  (
    'item_order' => 2
    'item_id' => 24
  )
)

This is the format update_batch understand. Third parameter specifies a field which is used by function to know which row to update.

Update batch separates data by 100 rows and then creates case-based queries, thus avoiding large amount of queries.

edited Feb 26, 2014 at 22:05

answered Feb 26, 2014 at 16:54

baldrs

2,1611 gold badge26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tom Over a year ago

I've slightly altered the array. Would this change your answer? What you're doing looks like what I'm trying to do...

M Khalid Junaid · Accepted Answer · 2014-02-26 16:51:52Z

0

Try this one in your model by making function

$item_ids = $item['item_id'];
$orders = $item['item_order'];
if (!empty($item_ids) && !empty($orders)) {
    for ($i = 0; $i < count($item_ids); $i++) {
        $data = array('item_order' => $orders[$i]);
        $this->db->where('item_id', $item_ids[$i]);
        $this->db->update('yourtablename', $data);
    }
}

Updating Data

answered Feb 26, 2014 at 16:51

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

3 Comments

Tom Over a year ago

Is there a way to do this without having to loop and do a new update query for each row?

M Khalid Junaid Over a year ago

I guess no you have to loop over the array because you cannot arrange the ids in sequence to their order

baldrs Over a year ago

@Tom see my answer, it does not use update in a cycle

Collectives™ on Stack Overflow

Codeigniter active record - update query using data from array

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related