在PHP中通过POST传递数组以插入MySQL [英] Passing an array via POST in PHP to be inserted into MySQL
问题描述
我有一个表格，允许用户将类和活动输入到多个字段中，这些字段的声明如下:
I have a form that allows users to input classes and activities into multiple fields, these fields are declared like this :
label for ="classact">Classes and Activities</label>
<input type = "text" name = "classact[0]" value ="" id ="classact[0]">
<input type = "text" name = "classact[1]" value ="" id ="classact[1]">
<input type = "text" name = "classact[2]" value ="" id ="classact[2]">
传递表单时，这是在插入中处理的代码:
When the form is passed this is the code that handles in the insert:
$maininsert = "INSERT INTO `camptest`
(`name`, `city`, `phone`, `photo`)
VALUES
('$_POST[name]', '$_POST[city]', '$_POST[phone]', '$photoinfo')
SET @lid = LAST_INSERT_ID()
";
$classactinsert = "INSERT INTO `class_act`
(`cid`";
for($i = 0; $i < 3; $i++)
{
if(isset($_POST['classact'][$i]))
{
$temp = $i+1;
$classactinsert = $classactinsert . ",`act$temp`";
}
}
$classactinsert = $classactinsert . ")
VALUES
('@lid'";
for($i = 0; $i < 3; $i++)
{
if(isset($_POST['classact'][$i]))
{
$classactinsert = $classactinsert . ",'$_POST[classact][$i]";
}
}
$classactinsert = $classactinsert . ")";
$indata = $maininsert . $classactinsert;
$result = mysql_query($indata);
我意识到有很多代码，但是在填写表格并提交后，就会生成查询:
I realize thats alot of code, but upon filling out the form and submitting this is the query that gets generated:
INSERT INTO `camptest` (`name`, `city`, `phone`, `photo`) VALUES ('Multiple Activities', 'Nowhere', '555-555-1111', 'images/51127f6b06d1e.jpg') SET @lid = LAST_INSERT_ID() INSERT INTO `class_act` (`cid`,`act1`,`act2`,`act3`) VALUES ('@lid','Array[0],'Array[1],'Array[2])
该查询未插入，但即使我将其打开，它也不会抛出任何错误.
The query is not inserting, but its not throwing back any errors either, even though I have them turned on.
我的主要问题是，我在做什么错，导致值act1，act2和act3分别显示为Array [0]，Array [1]和Array [2]?
My main question is, what am I doing wrong that is causing the values to act1, act2, and act3 to show up as Array[0], Array[1], and Array[2]?
我的第二个问题是，我是否甚至会以正确的方式进行操作?我对php有点陌生，恐怕我可能会很难做到这一点?
My secondary question is, am I even going about this the right way? I'm a little new to php and I'm afraid I might be doing this the hard way?
任何帮助将不胜感激，如果您需要任何其他信息，请告诉我.
Any help would be appreciated, let me know if you need any additional information.
推荐答案
它不会插入任何内容，因为(除其他外)您的查询字符串未正确构建.
It does not insert anything, because (among other things) your query string is not being built correctly.
('@lid','Array[0],'Array[1],'Array[2])
撇号被弄乱了.我想建议一种(更我认为)更清洁，更结构化的方式来执行您的任务:
The apostrophes are messed up. I'd like to suggest a (in my opinion) cleaner and more structured way to perform your task:
注意:您显然正在使用mysql _ *-stack，因此我的示例也基于它.但是请注意，此方法已被弃用.请使用mysqli或更高版本: PDO .
Note: You are obviously working with the mysql_*-stack, so my example is also based on it. But be aware that this is deprecated. Please use mysqli or even better: PDO instead.
<?php
$maininsert = "INSERT INTO `camptest`
(`name`, `city`, `phone`, `photo`)
VALUES
('{$_POST['name']}', '{$_POST['city']}', '{$_POST['phone']}', '$photoinfo')";
//perform the main insert and fetch the insert id
mysql_query($maininsert);
$last_id = mysql_insert_id();
// Put the keys and values of the acts in arrays. We can already
// populate them with the one key-value-pair we already know
$act_keys = array('cid');
$act_values = array($last_id);
foreach($_POST['classact'] as $key => $value) {
//walk through the POSTed acts and add them to the corresponding array
$act_keys[] = 'act'.($key+1);
$act_values[] = $value;
}
//Now build the whole string:
$insert_acts = "INSERT INTO `class_act`
(`" . implode("`, `", $act_keys) . "`)
VALUES
('" . implode("', '", $act_values) . "')";
//and finally perform the query:
mysql_query($insert_acts);
还请注意，此代码在 SQL注入上非常容易受到攻击，因此应绝对不能用于生产 ！！！确保使用准备好的语句(例如PDO)或/和正确清理输入内容.
Please also note that this code is highly vulnerable concerning SQL-Injection and should absolutely not be used in production!!! Make sure to either use prepared statements (like with PDO) or/and to sanitize your input properly.
此外，此解决方案只是我的建议，也是实现此目的的多种方法之一.但是，您要征求意见:) PHP是非常灵活的语言，因此很容易完成工作，但是有很多方法可以完成工作，因此总有机会选择困难的东西一个丑陋的.其他(尤其是强类型的)语言可能会设计使然.但是PHP确实很容易学习，并且我相信您的代码会逐渐改善:)
Also, this solution is just my suggestion and one of many ways to do it. But hey, you asked for an opinion :) PHP is very flexible language, so it's easy to get stuff done, but there are many ways to get it done, so there are always chances to pick a hard an ugly one. Other, especially strong typed languages might prevent that by design. But PHP is really easy to learn and I'm sure your code will improve gradually :)
我注意到的另一件事:您不需要在HTML中指定array-key，您只需要弄清楚它是名称后面带有[]的数组.另外，我不确定您使用的id属性是否有效，但是您可能希望使用更简单的方法:
Another thing I've noticed: You don't need to specify the array-keys in your HTML, you just need to make clear that it's an array with [] behind the name. Also, I'm not sure if the id-attributes you using are valid, but you might want use something more simple:
<input type="text" name="classact[]" value="" id="classact1">
<input type="text" name="classact[]" value="" id="classact2">
<input type="text" name="classact[]" value="" id="classact3">
在下一步中，您可能需要稍微重构代码以使其更加结构化和可读性.由于您要执行一项任务，即将某项插入表中"两次，因此我们还可以利用它来实现可重用的功能:
In the next step, you might want to refactor your code a little to make it even more structured and readable. Since you are performing one task, which is 'inserting something into a table', twice, we could also make a resusable function out of it:
<?php
function my_insert($table, $data) {
// We leverage the flexibility of associative arrays
$keys = "`" . implode("`, `", array_keys($data)) . "`";
$values = "'" . implode("', '", $data) . "'";
mysql_query("INSERT INTO `{$table}` ({$keys}) VALUES ({$values})");
return mysql_insert_id(); //This might come in handy...
}
//in order to use this function, we now put our data into associative arrays:
$class_insert = array(
'name' => $_POST['name'],
'city' => $_POST['city'],
'phone' => $_POST['phone'],
'photo' => $photoinfo
);
$class_insert_id = my_insert('camptest', $class_insert); //and pass it to the function
// You can either build the array while looping through the POSTed values like above,
// or you can pass them in directly, if you know that there will always be 3 values:
$activities_insert = array(
'cid' => $class_insert_id,
'act1' => $_POST['classact'][0],
'act2' => $_POST['classact'][1],
'act3' => $_POST['classact'][2]
);
$activities_insert_id = my_insert('class_act', $activities_insert);
肯定有足够的改进和优化空间-只是想向您展示PHP可以多么出色:-P
There sure is enough room for improvement and optimization - just wanted to show you how awesome PHP can be :-P
这篇关于在PHP中通过POST传递数组以插入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
