CODEWITHSUNDEEP
phpmysqlarrayscodeigniteractiverecord
Tom
Tom

Reputation: 12998

Codeigniter active record - update query using data from array

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

Upvotes: 0

Views: 2055

Answers (2)

baldrs
baldrs

Reputation: 2161

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.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 0

Related Questions