php 用一个查询插入多行 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12502032/
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
Insert multiple rows with one query MySQL
提问by CBeTJlu4ok
I want to write script, with function to multiple insert query. let me explain this better.
我想编写脚本，具有多个插入查询的功能。让我更好地解释一下。
I have one quantityinput in html form.
and I have MySQL query to insertregistered userinto the table.
so i want my function to insert this query for "quantity" times.
我有一个quantityhtml 形式的输入。我有 MySQL 查询要插入registered user到表中。所以我希望我的函数为“数量”时间插入这个查询。
mysql_query("INSERT INTO `pxlot` (realname,email,address,phone,status,regtime,ip)
VALUES ('$realname','$email','$address','$phone','0','$dateTime','$ip')")
or die (mysql_error()); // Inserts the user.
To insert this for example 3 times. Any suggestions?
例如插入 3 次。有什么建议？
回答by Michael Krelin - hacker
INSERT INTO table (a,b) VALUES (1,2), (2,3), (3,4);
回答by Ali Nawaz
In most cases inserting multiple records with one Insert statement is much faster in MySQL than inserting records with for/foreach loop in PHP.
在大多数情况下，在 MySQL 中使用一个 Insert 语句插入多条记录比在 PHP 中使用 for/foreach 循环插入记录要快得多。
Let's assume $column1 and $column2 are arrays with same size posted by html form.
让我们假设 $column1 和 $column2 是由 html 表单发布的具有相同大小的数组。
You can create your query like this:
您可以像这样创建查询：
<?php
$query = 'INSERT INTO TABLE (`column1`, `column2`) VALUES ';
$query_parts = array();
for($x=0; $x<count($column1); $x++){
$query_parts[] = "('" . $column1[$x] . "', '" . $column2[$x] . "')";
}
echo $query .= implode(',', $query_parts);
?>
If data is posted for two records the query will become:
如果为两条记录发布数据，则查询将变为：
INSERT INTO TABLE (
column1,column2) VALUES ('data', 'data'), ('data', 'data')
INSERT INTO TABLE (
column1,column2) VALUES ('data', 'data'), ('data', 'data')
回答by DiverseAndRemote.com
Here are a few ways to do it
这里有几种方法可以做到
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
select '$realname','$email','$address','$phone','0','$dateTime','$ip'
from SOMETABLEWITHTONSOFROWS LIMIT 3;
or
或者
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
select '$realname','$email','$address','$phone','0','$dateTime','$ip'
union all select '$realname','$email','$address','$phone','0','$dateTime','$ip'
union all select '$realname','$email','$address','$phone','0','$dateTime','$ip'
or
或者
INSERT INTO pxlot (realname,email,address,phone,status,regtime,ip)
values ('$realname','$email','$address','$phone','0','$dateTime','$ip')
,('$realname','$email','$address','$phone','0','$dateTime','$ip')
,('$realname','$email','$address','$phone','0','$dateTime','$ip')
回答by Hitesh Ranaut
Use below function to insert multiple rows in single query
使用以下函数在单个查询中插入多行
function multiple_insert($table, $fields = array(), $values = array(), $appendix = false, $ret = false) {
$query = 'INSERT INTO';
$query .= ' `' . $this->escape($table) . "`";
if (is_array($fields)) {
$query .= ' (';
$num = 0;
foreach ($fields as $key => $value) {
$query .= ' `' . $value . '`';
$num++;
if ($num != count($fields)) {
$query .= ',';
}
}
$query .= ' ) VALUES ';
foreach ($values as $key => $value) {
$query .= '(';
foreach ($value as $key => $value) {
$query .= "'" . $value . "'" . ',';
}
$query = rtrim($query, ',');
$query .= '),';
}
$query = rtrim($query, ',');
if ($appendix) {
$query .= ' ' . $appendix;
}
if ($ret) {
return $query;
}
$this->sql = $query;
$this->result = mysqli_query($this->mysql, $query);
if (mysqli_error($this->mysql) != '') {
$this->_error(mysqli_error($this->mysql));
$this->result = null;
return false;
} else {
return $this;
}
}
}
}
and this is how you will use the function
这就是您将如何使用该功能
$values_array = array(
['videoid1' , 'chneneid1' , 'descitpin1'] ,
['videoid2' , 'chneneid3' , 'descitpin3']
);
$table_columns = array('video_id' , 'channelId' , 'description');
multiple_insert($table_name , $table_columns , $values_array );
回答by MEX
If you would like to insert multiple values lets say from multiple inputs that have different post values but the same table to insert into then simply use:
如果您想插入多个值，可以从具有不同帖子值但要插入同一个表的多个输入中说出来，然后只需使用：
mysql_query("INSERT INTO `table` (a,b,c,d,e,f,g) VALUES
('$a','$b','$c','$d','$e','$f','$g'),
('$a','$b','$c','$d','$e','$f','$g'),
('$a','$b','$c','$d','$e','$f','$g')")
or die (mysql_error()); // Inserts 3 times in 3 different rows
回答by mohamed aslam
<?php
$datas = ['1','2','3'];
$datas = ['1','2','3'];
foreach ($datas as $key => $data) {
$query_value[] = "('" . $data . "', '" . $datas[$key] . "')";
}
echo $value_string = implode(",", $query_value);
?>
回答by Amrutha Munic
Here i used forech for multi row insertion..My code is in php and mysqli.. My first page is Add_timetable.php it views like this image..Preview of Add_timetable.phpResult preview is like this..Result Preview
多行insertion..My代码在这里，我用forech是在PHP和mysqli的..我的第一个页面是Add_timetable.php它认为这样的形象.. Add_timetable.php的预览结果预览是这样的..结果预览
This page is named as Add_timetable1.php
这个页面被命名为 Add_timetable1.php
foreach($_POST['days'] as $row=>$Days)
{include 'dbcon.php';
$s=mysqli_real_escape_string($conn,($_POST['cl']));
$n=mysqli_real_escape_string($conn,($_POST['di']));
$g=mysqli_real_escape_string($conn,($_POST['days'][$row]));
//echo $g[$i];
$a=mysqli_real_escape_string($conn,($_POST['p1'][$row]));
$b=mysqli_real_escape_string($conn,($_POST['p2'][$row]));
$c=mysqli_real_escape_string($conn,($_POST['p3'][$row]));
$d=mysqli_real_escape_string($conn,($_POST['p4'][$row]));
$e=mysqli_real_escape_string($conn,($_POST['p5'][$row]));
$ins=mysqli_query($conn,"select Timetable_Id from key_table");
while ($row=mysqli_fetch_assoc($ins))
{
$att=$row['Timetable_Id']+1;
$_SESSION['pp']=$att;
}
$ttid=$_SESSION['pp'];
$ins1=mysqli_multi_query($conn,"insert into
tbl_timetable(T_Id,Class,Division,Days,period1,period2,period3,period4,period5)
values('$ttid','$s', '$n', '$g','$a','$b','$c','$d','$e')");
$upd=mysqli_query($conn,"update Key_table set Timetable_Id ='$ttid'");
//$ins2=mysqli_query($conn,"insert into tbl_login values('$u','$pas','Staff')");
}
?>
<script>
alert("Timetable added successfully");
window.location="Add_Timetable.php";
</script>
You can try this code i am it works correctly..
