php 使用foreach数组使用php将多行插入mysql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1816979/
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 into mysql with php using foreach arrays
提问by jrutter
Im stuck, been trying to figure this out for 2 hours now. I have figured out the foreach loop, but cant figure out how to insert the data now.
我被卡住了，现在已经试图解决这个问题 2 个小时了。我已经弄清楚了 foreach 循环，但现在无法弄清楚如何插入数据。
here is my php, what am I doing wrong?
这是我的 php，我做错了什么？
$query = "INSERT INTO images (thumb_path, image_path, main_image, project_id) VALUES ";
foreach($_POST as $key => $value) {
$query .= "$thumb_path,$image_path,$main_image,$_POST[project_id])";
$result = mysql_query($query, $connection);
}
Thanks!
谢谢！
Should I lay it out like this, sorry still a newbie to foreach and how it works.
我应该像这样布置它，抱歉仍然是 foreach 的新手以及它是如何工作的。
foreach($_POST as $key => $value) {
$query = "INSERT INTO images VALUES (thumb_path, image_path, main_image, project_id),";
$query .= "$value[thumb_path], $value[$image_path], $value[$main_image], '$_POST[project_id'])";
}
$result = mysql_query($query, $connection);
回答by Ben James
Several things wrong here:
这里有几个错误：
- You're missing an opening bracket on your list of values (after
VALUES) - You are running
mysql_queryon every iteration of theforeach. Instead, you probably want to build the string first (using multiple values lists), and runmysql_queryoutside the loop. - You can't interpolate the
$_POST['project_id']variable into a string in that way - You need to escape the
$_POSTdata before putting it into the database! - You are not actually using the
$keyor$valuefrom your foreach in your query, you're just discarding it. - The variables inside the loop (
$thumb_pathetc.) will be the same for every iteration of the loop—so you're going to insert the same data every time. - The logic of the loop just doesn't make sense. You don't know how long
$_POSTis, or what might be in it, so why are you looping over$_POST?
- 您的值列表中缺少一个左括号（之后
VALUES） - 您正在运行
mysql_query上的每一次迭代foreach。相反，您可能希望首先构建字符串（使用多个值列表），然后mysql_query在循环外运行。 - 您不能
$_POST['project_id']以这种方式将变量插入到字符串中 $_POST在将数据放入数据库之前，您需要对数据进行转义！- 您实际上并没有在查询中使用foreach 中的
$key或$value，您只是丢弃了它。 - 循环内的变量（
$thumb_path等）在循环的每次迭代中都是相同的——因此您每次都将插入相同的数据。 - 循环的逻辑没有意义。你不知道有多长
$_POST，或者里面可能有什么，所以你为什么要循环$_POST？
Some hints to help you fix all this:
一些提示可帮助您解决所有这些问题：
- Check your error log.
var_dumpthe string of SQL before you execute the query to check if it is syntactically and logically correct.
- 检查您的错误日志。
var_dump执行查询之前的 SQL 字符串，以检查它在语法和逻辑上是否正确。
If you want further help, I'd suggest you var_dumpwhat is in $_POST, try to write what you think the query ought to look like, and then post both here. Then maybe someone will help you go from one to the other.
如果您需要进一步的帮助，我建议您提供 中的var_dump内容$_POST，尝试编写您认为查询应该是什么样子，然后将两者都张贴在这里。然后也许有人会帮助你从一个到另一个。
回答by Marc B
I find something like this is far easier to maintain than a repeated string concatenation as you're doing:
我发现这样的事情比你正在做的重复字符串连接更容易维护：
$values = array();
foreach ($_POST as $key => $value) {
$qvalue = mysql_real_escape_string($value);
$values[] = "($field1, $field2, $field3, $qvalue)"; // quoted value, not the raw value
}
$query_values = implode(',', $values);
$query = "INSERT INTO images (field1, field2, field3, field4) VALUES $query_values";
$result = mysql_query($query, $connection);
Just keep in mind that when building a query like this, it's entirely possible to build a query large enough to except the max_packet length, in which case you'd have to split the insert into multiple smaller queries.
请记住，在构建这样的查询时，完全有可能构建一个足够大的查询，以排除 max_packet 长度，在这种情况下，您必须将插入拆分为多个较小的查询。
回答by Aif
回答by Rob
// escape your input
$_POST = array_map('addslashes', $_POST);
// rather than recursively calling mysql_query, you can insert all your rows with one query
// INSERT INTO table (columns) VALUES (data), (data), (data), ...
$values = array();
foreach($_POST as $key => $value) {
$values[] = "('{$_POST['thumb_path']}', '{$_POST['image_path']}', '{$_POST['main_image']}', '{$_POST['project_id']}')";
}
if(sizeof($values)) {
$query = "INSERT INTO images (thumb_path, image_path, main_image, project_id) VALUES ".implode(',', $values);
$result = mysql_query($query, $connection);
}
