Codeigniter Insert Multiple Rows in SQL

Question

I am fresh to Codeigniter. I have a form which looks something like this.

<tr>
<td><input type="text" name="Name[0]" value=""></td>
<td><input type="text" name="Address[0]"  value=""><br></td>
<td><input type="text" name="Age[0]" value=""></td>
<td><input type="text" name="Email[0]" value=""></td>
</tr>



<tr>
<td><input type="text" name="Name[1]" value=""></td>
<td><input type="text" name="Address[1]"  value=""><br></td>
<td><input type="text" name="Age[1]" value=""></td>
<td><input type="text" name="Email[1]" value=""></td>
</tr>

There may be from 0 to n rows, usually 5 to 10 rows. How do I insert them in SQL? Is this possible with Codeigniter or should I use a native PHP script?

$name=$_POST['Name'];
$address=$_POST['Address'];
$age=$_POST['Age'];
$email=$_POST['Email'];
$count = count($_POST['Name']);



for($i=0; $i<$count; $i++) {
$data = array(
           'name' => $name[$i], 
           'address' => $address[$i],
           'age' => $age[$i],
           'email' => $email[$i],

           );


  $this->db->insert('mytable', $data);
}

I did this. It works. But the solution seems inelegant.

kevtrout's answer looks better but is currently throwing a lot of errors.

Is there any way to insert all data at one go?

See my solution http://stackoverflow.com/a/14332078/1045444... You have accepted solution which not most efficient... We should use insert_batch(); — Somnath Muluk, Apr 25 '13 at 11:56
@SomnathMuluk You are correct. Please see the linked question which I asked later http://stackoverflow.com/questions/4166146/codeigniter-inserting-multidimensional-array-as-rows-in-mysql?lq=1 — Mr Hyde, Apr 26 '13 at 19:28

score 24 · Answer 1 · answered Jan 15 '13 at 06:14

24

Multiple insert/ batch insert is now supported by codeigniter. It will firing one query rather than firing too many queries.

$data =array();
for($i=0; $i<$count; $i++) {
$data[$i] = array(
           'name' => $name[$i], 
           'address' => $address[$i],
           'age' => $age[$i],
           'email' => $email[$i],

           );
}

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

answered Jan 15 '13 at 06:14

Somnath Muluk

55,015
38
216
226

This assumes the driver supports it. It doesn't work with CodeIgniter 2.1.4 and the sqlsrv driver. – Charity Leschinski Jan 13 '14 at 05:05

score 8 · Accepted Answer · answered Oct 03 '10 at 15:09

Make your form like this:

<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>

Then you can simply do:

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

On closer analysis, it is very similar to kevtrout's solution, $this->perhaps->more->elegant(); Must learn more about arrays. — Mr Hyde, Oct 03 '10 at 18:08
Have a look at `insert_batch()` method [http://codeigniter.com/user_guide/database/active_record.html] — Shahid, Aug 14 '11 at 10:55

kevtrout · Answer 3 · 2010-10-03T13:03:52.810

The form you show will create a $_POST array with indexes of name, address, age, and email. Each of these will contain the n number of "rows" your form provides. For example:

array(
    'name' => array('First Name','Second Name'),
    'address' => array ('First Address','Second Address'),
    'age' => array('First Age','Second Age'),
    'email' => array('First Email', 'Second Email')
    );

You may want to rearrange that array into one where each index of the array is a "person". This will make inserting the information into your database simpler.

//subtract 1 from below to account for the assumed submit button
$number_of_rows = count($_POST)-1;

for($i=0;$i<$number_of_rows;$i++){
    $person[]['name'] = $this->input->post('Name')[$i];
    $person[]['address'] = $this->input->post('Address')[$i];
    $person[]['age'] = $this->input->post('Age')[$i];
    $person[]['email'] = $this->input->post('Email')[$i];
    }

This will create something like this:

array(
    0=>array('First Name','First Address','First Age','First Email'),
    1=>array ('Second Name','Second Address','Second Age','Second Email') 
    );

Now you can use a loop to insert each person into the db.

for($y=0;$y<count($person);$y++){
    $this->db->insert('mytable',$person[$y];
}

What errors are being thrown? I fixed a syntax error with `$this->input->post{'Name');` above. — kevtrout, Oct 03 '10 at 13:03
Did that! Part of a Ajax Call with complex return function, thats why had to substitute a simpler example above. See my answer below. Doesn't seem to be the Codeigniter way. — Mr Hyde, Oct 03 '10 at 13:16
It seems that `$this->input->post()` doesn't work like an array, so calling it with an index doesn't work. There's nothing wrong with your posted answer. One of the benefits of CodeIgniter is it's flexibility. you can run regular php alongside built in functions. — kevtrout, Oct 03 '10 at 20:22

Codeigniter Insert Multiple Rows in SQL

3 Answers3

Linked