php 如何使用php将数据数组插入到mysql中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15013211/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert array of data into mysql using php
提问by BaconJuice
Currently I have an Array that looks like the following when output thru print_r();
目前我有一个数组，当通过 print_r() 输出时，它看起来像下面这样；
Array
(
[0] => Array
(
[R_ID] => 32
[email] => [email protected]
[name] => Bob
)
[1] => Array
(
[R_ID] => 32
[email] => [email protected]
[name] => Dan
)
[2] => Array
(
[R_ID] => 32
[email] => [email protected]
[name] => Paul
)
[3] => Array
(
[R_ID] => 35
[email] => [email protected]
[name] => Mike
)
)
I would like to insert this data into one table with each element value belonging to its respective field.
我想将此数据插入到一个表中，每个元素值都属于其各自的字段。
Currently my php code looks like the following
目前我的 php 代码如下所示
if(is_array($EMailArr)){
foreach($EMailArr as $R_ID => $email => $name){
$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
mysql_query($sql) or exit(mysql_error());
}
}
*Note : R_ID is NOT the primary key in this table.*
*注意：R_ID 不是此表中的主键。*
Can someone help me understand how I should approach this situation? Thank you for reading and your help!
有人可以帮助我了解我应该如何处理这种情况吗？感谢您的阅读和帮助！
Regards.
问候。
回答by dxvargas
I would avoid to do a query for each entry.
我会避免对每个条目进行查询。
if(is_array($EMailArr)){
$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ";
$valuesArr = array();
foreach($EMailArr as $row){
$R_ID = (int) $row['R_ID'];
$email = mysql_real_escape_string( $row['email'] );
$name = mysql_real_escape_string( $row['name'] );
$valuesArr[] = "('$R_ID', '$email', '$name')";
}
$sql .= implode(',', $valuesArr);
mysql_query($sql) or exit(mysql_error());
}
回答by Chris
First of all you should stop using mysql_*. MySQL supports multiple inserting like
首先，您应该停止使用 mysql_*。MySQL 支持多次插入，如
INSERT INTO example
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
You just have to build one stringin your foreach loop which looks like that
你只需要在你的 foreach 循环中构建一个看起来像这样的字符串
$values = "(100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1')";
and then insert it after the loop
然后在循环后插入
$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES ".$values;
Another way would be Prepared Statements, which are even more suited for your situation.
另一种方法是Prepared Statements，它更适合您的情况。
回答by Green Black
if(is_array($EMailArr)){
foreach($EMailArr as $key => $value){
$R_ID = (int) $value['R_ID'];
$email = mysql_real_escape_string( $value['email'] );
$name = mysql_real_escape_string( $value['name'] );
$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
mysql_query($sql) or exit(mysql_error());
}
}
A better example solution with PDO:
一个更好的 PDO 示例解决方案：
$q = $sql->prepare("INSERT INTO `email_list`
SET `R_ID` = ?, `EMAIL` = ?, `NAME` = ?");
foreach($EMailArr as $value){
$q ->execute( array( $value['R_ID'], $value['email'], $value['name'] ));
}
回答by Bairu
I've a PHP library which helps to insert array into MySQL Database. By using this you can create update and delete. Your array key value should be same as the table column value. Just using a single line code for the create operation
我有一个 PHP 库，它有助于将数组插入 MySQL 数据库。通过使用它，您可以创建更新和删除。您的数组键值应与表列值相同。只需使用一行代码进行创建操作
DB::create($db, 'YOUR_TABLE_NAME', $dataArray);
where $db is your Database connection.
其中 $db 是您的数据库连接。
Similarly, You can use this for update and delete. Select operation will be available soon. Github link to download : https://github.com/pairavanvvl/crud
同样，您可以使用它进行更新和删除。选择操作将很快可用。Github 下载链接：https: //github.com/pairavanvvl/crud
