一次将多个值插入MySQL [英] Inserting multiple values into a MySQL at once
问题描述
有人能阐明为什么此PHP/MySQL无法正常工作吗?基本上,我需要一次从表单中插入行的负载,因此将有多个名称字段,多个短,中,长字段等.我收到此错误:
could anyone shed some light as to why this PHP / MySQL is not working? Basically I need to insert loads of rows at once from a form, so there will be multiple name fields, multiple short, med, long fields etc.. Im getting this error:
Notice: Undefined variable: Short1 in C:\xampp\htdocs\process.php on line 95
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Long, VLong, Extreme, LJump, HJump, Shotputt, Discuss, Javelin, Date, Year) VAL' at line 2
这是我的PHP
<?php
$host = "localhost";
$databasename = "pe_results";
$databaseusername = "root";
$databasepassword = "";
$conn = mysql_connect("$host", "$databaseusername", "$databasepassword");
mysql_select_db("$databasename", $conn);
if (isset($_POST['Name1'])) {
$Name1 = $_POST['Name1'];
}
if (isset($_POST['Short1'])) {
$Short1 = $_POST['Short1'];
}
if (isset($_POST['Med1'])) {
$Med1 = $_POST['Med1'];
}
if (isset($_POST['Long1'])) {
$Long1 = $_POST['Long1'];
}
if (isset($_POST['VLong1'])) {
$VLong1 = $_POST['VLong1'];
}
if (isset($_POST['Extreme1'])) {
$Extreme1 = $_POST['Extreme1'];
}
if (isset($_POST['LJump1'])) {
$LJump1 = $_POST['LJump1'];
}
if (isset($_POST['HJump1'])) {
$HJump1 = $_POST['HJump1'];
}
if (isset($_POST['Shotputt1'])) {
$Shotputt1 = $_POST['Shotputt1'];
}
if (isset($_POST['Discuss1'])) {
$Discuss1 = $_POST['Discuss1'];
}
if (isset($_POST['Javelin1'])) {
$Javelin1 = $_POST['Javelin1'];
}
if (isset($_POST['Date'])) {
$Date = $_POST['Date'];
}
if (isset($_POST['Year'])) {
$Year = $_POST['Year'];
}
// Sector 2 */
if (isset($_POST['Name2'])) {
$Name2 = $_POST['Name2'];
}
if (isset($_POST['Short2'])) {
$Short2 = $_POST['Short2'];
}
if (isset($_POST['Med2'])) {
$Med2 = $_POST['Med2'];
}
if (isset($_POST['Long2'])) {
$Long2 = $_POST['Long2'];
}
if (isset($_POST['VLong2'])) {
$VLong2 = $_POST['VLong2'];
}
if (isset($_POST['Extreme2'])) {
$Extreme2 = $_POST['Extreme2'];
}
if (isset($_POST['LJump2'])) {
$LJump2 = $_POST['LJump2'];
}
if (isset($_POST['HJump2'])) {
$HJump2 = $_POST['HJump2'];
}
if (isset($_POST['Shotputt2'])) {
$Shotputt2 = $_POST['Shotputt2'];
}
if (isset($_POST['Discuss2'])) {
$Discuss2 = $_POST['Discuss2'];
}
if (isset($_POST['Javelin2'])) {
$Javelin2 = $_POST['Javelin2'];
}
if (isset($_POST['Date'])) {
$Date = $_POST['Date'];
}
if (isset($_POST['Year'])) {
$Year = $_POST['Year'];
}
$sql="INSERT INTO results_main
(Name, Short, Med, Long, VLong, Extreme, LJump, HJump, Shotputt, Discuss, Javelin, Date, Year)
VALUES
('$Name1', '$Short1', '$Med1', '$Long1', '$VLong1', '$Extreme1', '$LJump1', '$HJump1', '$Shotputt1', '$Discuss1', '$Javelin1', '$Date', '$Year'),
('$Name2', '$Short2', '$Med2', '$Long2', '$VLong2', '$Extreme2', '$LJump2', '$HJump2', '$Shotputt2', '$Discuss2', '$Javelin2', '$Date', '$Year');
";
$result = mysql_query($sql) or die(mysql_error());
// close connection
mysql_close($conn);
?>
JW的新错误消息
Notice: Undefined variable: Short1 in C:\xampp\htdocs\process.php on line 95
INSERT INTO results_main (`Name`, `Short`, `Med`, `Long`, `VLong`, `Extreme`, `LJump`, `HJump`, `Shotputt`, `Discuss`, `Javelin`, `Date`, `Year`) VALUES (`1`, ``, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `1`, `2013-04-26`, `10`), (`2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2`, `2013-04-26`, `10`); Unknown column '1' in 'field list'
推荐答案
LONG
是保留关键字,恰好是您列的名称.为了避免语法错误,列名应使用反引号进行转义.
LONG
is a reserved keyword and happens to be the name of your column. In order to avoid syntax error, the column name should be escape with backticks.
INSERT INTO results_main(Name, Short, Med, `Long`, VLong, ...) VALUES (....)
- MySQL保留关键字列表
- MySQL Reserved Keywords List
如果您有权更改列,请将名称更改为非保留关键字,以避免将来再次出现问题.
If you have the privilege to alter the column, change the name to a non-reserved keyword to avoid problem getting back on the future.
作为一个附带说明,如果值( s)是 SQL Injection
,则该查询容易受到攻击)的变量来自外部.请查看下面的文章,以了解如何防止这种情况的发生.通过使用PreparedStatements
,您可以摆脱在值周围使用单引号的情况.
As a sidenote, the query is vulnerable with SQL Injection
if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements
you can get rid of using single quotes around values.
这篇关于一次将多个值插入MySQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!