MySQL Codeigniter 在 SQL 中插入多行

Codeigniter Insert Multiple Rows in SQL

提问by Mr Hyde

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

我对 Codeigniter 很陌生。我有一个看起来像这样的表格。

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

可能有 0 到 n 行，通常是 5 到 10 行。如何在 SQL 中插入它们？这可以用 Codeigniter 还是我应该使用本机 PHP 脚本？

---

---

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

kevtrout 的答案看起来更好，但目前抛出了很多错误。

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

有没有办法一次性插入所有数据？

回答by Somnath Muluk

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

codeigniter 现在支持多插入/批量插入。它将触发一个查询而不是触发太多的查询。

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

回答by Mischa

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

回答by kevtrout

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:

您显示的表单将创建一个 $_POST 数组，其中包含姓名、地址、年龄和电子邮件的索引。其中每一个都将包含您的表单提供的 n 个“行”。例如：

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