Update a batch of rows with CodeIgniter

Question

I have been looking around but I have not found an answer yet. Kindly help if you know the answer.

How do you update multiple rows in CI?

In my MySQL:

I have column names:

ID, Settings Name, Settings Value ( Settings Name is Unique )

I have the ff Data:

ID = 1, Settings Name = "Hello" , Settings Value = "True"
ID = 2, Settings Name = "World", Settings Value = "Good"

and more ...

I also have a form that gets the Settings Value but I am not sure how to update it on the DB. How to update the True for the Hello being the Settings Name and update the Good for the World.

I heard about insert_batch() but is there an update_batch()?

I think it might help you stackoverflow.com/questions/7759514/… — Edwin Alex
– Edwin Alex, Commented Jan 8, 2013 at 9:48

Community · Accepted Answer · 2017-07-01 10:16:41Z

24

Are you using Active record?

Yes there is an update batch: $this->db->update_batch();

$data = array(
array(
  'ID' => 1 ,
  'Settings Name' => 'Hello' ,
  'Settings Value' => 'True'
),
array(
  'ID' => '2' ,
  'Settings Name' => 'World' ,
  'Settings Value' => 'Good'
)
);    

$this->db->update_batch('mytable', $data, 'where_key');

From the documentation:

The first parameter will contain the table name, the second is an associative array of values, the third parameter is the where key.

edited Jul 1, 2017 at 10:16

CommunityBot

11 silver badge

answered Jan 8, 2013 at 9:47

Bryan CS

6116 silver badges19 bronze badges

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

3 Comments

jsdecena Over a year ago

Thanks for this!!! I have been staring at that same documentation from CI but I just have the light bulb when you answer this Q. hehe foolish me.. Thanks again!

jsdecena Over a year ago

I have a follow up Qs: How to retrieve from the DB the posted values? is it the normal set_value() ? Thanks!

Bryan CS Over a year ago

@JeffSimonsDecena, what do you mean retrieve from DB with normal set_value()? The set_value() in CI's documentation is used to repopulate a form. Retrieving data from the DB can be found here Selecting Data

cchana · Accepted Answer · 2013-01-08 09:48:27Z

8

There is indeed an update_batch() method available in CodeIgniter already.

You can use it your example like so:

$data = array(
    array(
        'ID' => 1,
        'Settings Name' => 'Hello',
        'Settings Value' => 'True'
    ),
    array(
        'ID' => 2,
        'Settings Name' => 'World',
        'Settings Value' => 'Good'
    )
);
$this->db->update_batch('tableName', $data, 'id');

So what you have is an array of arrays, the children basically hold the data for each row in the database. The first parameter for update_batch() is the name of the database table, the second is the $data variable and the third is the column you want to use in the WHEN clause.

answered Jan 8, 2013 at 9:48

cchana

5,0123 gold badges36 silver badges45 bronze badges

Comments

Community · Accepted Answer · 2017-06-30 07:36:31Z

1

Here is a simple php code for perform this operations.

<?php
function basic_update($uwi='') {
$this->load->database();
$data = array(
'ID' => 1,
'Settings Name' => 'Hello',
'Settings Value' => 'True'  
);
$this->db->where('ID', '1');
$this->db->update('<table name>', $data);
$data1 = array(
    'ID' => 2,
    'Settings Name' => 'World',
    'Settings Value' => 'Good'
    );
    $this->db->where('ID', '2');
    $this->db->update('<table name>', $data1);
}

In $this->db->where('ID', '1'); ID is your table field and 1 is the value. In array first parameter will contain the table name, the second parameter is an associative array of values

edited Jun 30, 2017 at 7:36

CommunityBot

11 silver badge

answered Jan 8, 2013 at 10:53

Dev S.

312 bronze badges

1 Comment

Ed T. Over a year ago

That's a bad way of doing multiple row updates, as you are running 2 or more queries, where you can run only 1.

Collectives™ on Stack Overflow

Update a batch of rows with CodeIgniter

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related