使用MySQLi Prepared语句将动态多行输入数据库？ [英] Use MySQLi Prepared Statement to Enter Dynamic, Multiple Rows into Database?

问题描述

我创建了一个HTML表单，用户可以提交他们的姓名，电子邮件和语言技能。由于用户可能会说多种语言，因此用户可以通过点击添加按钮生成其他行。

然后将提交表单并输入一个MySQL数据库。我使用两个表来保存接收的数据，而第一个表中的用户ID将被auto_incremented：

  | （user-id）|名称|电子邮件| 
 | user-id |语言|熟练| 
  

现在我想写PHP代码，看起来像这样：

  $ name = $ _POST ['name']; 
 $ email = $ _POST ['email']; 
 
 $ add_table1 = $ mysqli-> prepare（INSERT INTO table1（name，email）VALUES（？，？））; 
 $ add_table2 = $ mysqli-> prepare（INSERT INTO table2（user_id，language，proficiency）VALUES（？，？，？））; 
 
 $ add_table1> bind_param（ss，$ name，$ email）; 
 $ add_table1-> execute（）; 
 $ user-id = $ mysqli-> insert_id; 
 
 foreach（$ _ POST ['language'] as $ index => $ language）{
 $ index = intval（$ index）; 
 $ language = mysql_real_escape_string（$ language）; 
 $ proficiency = mysql_real_escape_string（$ _ POST ['proficiency'] [$ index]）; 
 $ add_table2-> bind_param（iss，$ user-id，$ language，$ proficiency）; 
 $ add_table2-> execute（）; 
} 
 $ add_table1> close（）; 
 $ add_table2-> close（）; 
 $ mysqli-> close（）; 
  

表格应如下所示：

  | 1 |马克| [e-mail] | 
 | 2 | Sue | [e-mail] | 
 
 | 1 |英语|完美| 
 | 1 |西班牙语|真棒| 
 | 2 |英语|伟大| 
 | 2 |法语|确定| 
 | 2 |韩语|流利| 
  

但是，我的代码表1看起来不错，但表2看起来像这样

  | 1 | | | 
 | 2 | | | 
  

有人可以帮忙吗？感谢！

解决方案

我应该指出的一点是，不要使用 mysql_real_escape_string $ user-id 不是有效的变量名称。您不能使用连字符。

编辑：

事情打开错误报告，并输出 mysqli / mysqli_stmt :: $ error 大多数问题都可以用这些解决。

I have created a HTML form where users would submit their name, email, and language skills. Since a user may speak more than one language, user will be able to generate additional rows by clicking an "add" button.

The form will then be submitted and entered into a MySQL database. I'm using two tables to hold the received data, whereas the user-id in the first table will be auto_incremented:

| (user-id) | name | email|
| user-id | language | proficiency|

Now I'm trying to write the PHP code, which looks something like this:

$name = $_POST['name'];
$email = $_POST['email'];

$add_table1 = $mysqli->prepare("INSERT INTO table1 (name, email) VALUES (?, ?)");
$add_table2 = $mysqli->prepare("INSERT INTO table2 (user_id, language, proficiency) VALUES (?, ?, ?)");

$add_table1->bind_param("ss", $name, $email);
$add_table1->execute();
$user-id = $mysqli->insert_id;

foreach($_POST['language'] as $index => $language) {
    $index = intval($index);
    $language = mysql_real_escape_string($language);
    $proficiency = mysql_real_escape_string($_POST['proficiency'][$index]);
    $add_table2->bind_param("iss", $user-id, $language, $proficiency);
    $add_table2->execute();
}
$add_table1->close();
$add_table2->close();
$mysqli->close();

The table should look like this

| 1 | Mark | [e-mail] |
| 2 | Sue  | [e-mail] |

|1 | English | perfect |
|1 | Spanish | awesome |
|2 | English | great |
|2 | French | ok |
|2 | Korean | fluent |

However, with my code table 1 looks fine, but table 2 looks like this

| 1 |  |  |
| 2 |  |  |

Can somebody help? Thanks!

解决方案

One thing I should point out is that you don't use mysql_real_escape_string with prepared statements.

Another thing is that $user-id is not a valid variable name. You can't use a hyphen.

Edit:

It's a good thing to turn on error reporting and to output mysqli/mysqli_stmt::$error when anything fails. Most problems can be resolved with these.

这篇关于使用MySQLi Prepared语句将动态多行输入数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！