Codeigniter Inserting Multidimensional Array as rows in MySQL

Question

Please Refer to this question I asked
Codeigniter Insert Multiple Rows in SQL

To restate

<tr>
<td><input type="text" name="user[0][name]" value=""></td>
<td><input type="text" name="user[0][address]" value=""><br></td>
<td><input type="text" name="user[0][age]" value=""></td>
<td><input type="text" name="user[0][email]" value=""></td>
</tr>
<tr>
<td><input type="text" name="user[1][name]" value=""></td>
<td><input type="text" name="user[1][address]" value=""><br></td>
<td><input type="text" name="user[1][age]" value=""></td>
<td><input type="text" name="user[1][email]" value=""></td>
</tr>
..........

Can Be Inserted into MySQL as this

 foreach($_POST['user'] as $user)
{
    $this->db->insert('mytable', $user);
}

This results in multiple MySQL queries. Is it possible to optimise it further, so that the insert occurs in one query

Something like this

insert multiple rows via a php array into mysql

but taking advantage of codeigniters simpler syntax. Thanks

Unless you have hundreds of thousands of records to INSERT, you won't gain anything and the real bottleneck are the table indexes, and not the round-trip cost of running the query multiple times. — Milan Babuškov
– Milan Babuškov, Commented Nov 12, 2010 at 15:34

Patrick M · Accepted Answer · 2014-10-16 19:37:03Z

4

$this->db->insert_batch('table_name', $data);

Check the code igniter user guide for more details.

edited Oct 16, 2014 at 19:37

Patrick M

11k9 gold badges74 silver badges105 bronze badges

answered Jan 4, 2011 at 2:51

Mr Hyde

3,4138 gold badges39 silver badges48 bronze badges

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

Comments

Halil Özgür · Accepted Answer · 2010-11-12 18:29:21Z

0

CodeIgniter does not seem to have a mutli-insert method. Moreover, even if it had, you may run into issues like:

Hitting the maximum query length cap
Having to lock the table until inserts are done
Dealing with errors can be a bit harder
...

answered Nov 12, 2010 at 18:29

Halil Özgür

16k6 gold badges51 silver badges58 bronze badges

Comments

mos fetish · Accepted Answer · 2010-11-13 08:48:20Z

0

Codeigniter 2 (upcoming release) will have a batch insert method.

answered Nov 13, 2010 at 8:48

mos fetish

5062 silver badges13 bronze badges

1 Comment

Mr Hyde Over a year ago

thanks, i am already using ci 2, will look up the documentation.

Gene · Accepted Answer · 2011-01-02 02:47:15Z

0

In CI 2, there is a set_insert_batch() Active Record function.

answered Jan 2, 2011 at 2:47

Gene

191 silver badge3 bronze badges

1 Comment

Mr Hyde Over a year ago

No documentation though, and an array I assembled doesn't get inserted

Collectives™ on Stack Overflow

Codeigniter Inserting Multidimensional Array as rows in MySQL

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related