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, Nov 12 '10 at 15:34

score 4 · Accepted Answer · edited Oct 16 '14 at 19:37

4

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

Check the code igniter user guide for more details.

edited Oct 16 '14 at 19:37

Patrick M

10,547
9
68
101

answered Jan 04 '11 at 02:51

Mr Hyde

3,393
8
36
48

score 0 · Answer 2 · answered Nov 12 '10 at 18:29

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 '10 at 18:29

Halil Özgür

15,731
6
49
56

score 0 · Answer 3 · answered Nov 13 '10 at 08:48

0

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

answered Nov 13 '10 at 08:48

mos fetish

506
2
13

thanks, i am already using ci 2, will look up the documentation. – Mr Hyde Nov 13 '10 at 11:08

score 0 · Answer 4 · answered Jan 02 '11 at 02:47

0

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

answered Jan 02 '11 at 02:47

Gene

19
1
3

1

No documentation though, and an array I assembled doesn't get inserted – Mr Hyde Jan 03 '11 at 01:59

Codeigniter Inserting Multidimensional Array as rows in MySQL

4 Answers4

Linked