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?

Solution 1

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);
}

Solution 2

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

Solution 3

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];
}

Comments

[kevtrout]

What errors are being thrown? I fixed a syntax error with $this->input->post{'Name'); above.

[Mr Hyde]

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]

On closer analysis, it is very similar to kevtrout's solution, $this->perhaps->more->elegant(); Must learn more about arrays.

[kevtrout]

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.

[Shahid]

Have a look at insert_batch() method [codeigniter.com/user_guide/database/active_record.html]

[Charity Leschinski]

This assumes the driver supports it. It doesn't work with CodeIgniter 2.1.4 and the sqlsrv driver.