php SQL 查询 INSERT 不起作用将值插入我的数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5171645/
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
SQL query INSERT not working inserting values into my DB
提问by Aiden Ryan
I'm trying to insert some values into my DB but it's not working, i'm trying to figure out why it's not working but I'm an amateur php coder,
我正在尝试将一些值插入到我的数据库中，但它不起作用，我试图弄清楚为什么它不起作用，但我是一名业余 php 编码员，
This is the code I'm using:
这是我正在使用的代码：
$insert = mysql_query
("
INSERT INTO news(id,title,body,date,by)
VALUES ('NULL','".$title."','".$body."','".$date."','".$by."')
");
mysql_close($connect);
And the rows i'm trying to insert into are: id,title,body,date,by
but it's not showing up in the database or on my news page.
我试图插入的行是：id,title,body,date,by
但它没有显示在数据库或我的新闻页面上。
Can someone please help me?
有人可以帮帮我吗？
回答by mellamokb
byis a special keyword. Try wrapping the column names in tick marks:
by是一个特殊的关键字。尝试将列名包裹在刻度线中：
INSERT INTO news(`id`,`title`,`body`,`date`,`by`)
回答by BillThor
I would expect id to be your primary key. It should not allow a null value. If it is auto incrementing you might try this:
我希望 id 是你的主键。它不应允许空值。如果它是自动递增的，你可以试试这个：
$insert = mysql_query
("
INSERT INTO news(title,body,date, by)
VALUES ('".$title."','".$body."','".$date."','".$by."')
");
Others have noted by is a reserved word so you will need to quote it. It is best to avoid naming database objects using reserved words. Consider renaming the bycolumn to author.
其他人注意到 by 是保留字，因此您需要引用它。最好避免使用保留字命名数据库对象。考虑将by列重命名为author.
You should consider making changing the query into a prepared statement. See How can I prevent SQL injection in PHP?.
您应该考虑将查询更改为准备好的语句。请参阅如何防止 PHP 中的 SQL 注入？.
回答by OMG Ponies
I recommend using sprintf& mysql_real_escape_stringto handle SQL injection attack possibilities:
我建议使用sprintf和mysql_real_escape_string来处理 SQL 注入攻击的可能性：
$insert = sprintf("INSERT INTO news
(id, title, body, date, `by`)
VALUES
(NULL,'%s','%s','%s','%s')",
mysql_real_escape_string($title),
mysql_real_escape_string($body),
mysql_real_escape_string($date),
mysql_real_escape_string($by));
$result = mysql_query($insert) or die(mysql_error());
This will let you know what error is encountered too.
这也会让您知道遇到了什么错误。
That said, there are numerous potential issues:
也就是说，有许多潜在的问题：
- by is a reserved keyword, you need backticks to escape its use in MySQL (see example above). The alternative is to rename the column to something that is not a reserved word -- backticking every column and/or table name hides such issues rather than learning
- NULL should not be inside of single quotes, or it will be interpreted as a string;
DEFAULTwould be another option if there's a DEFAULT constraint on the column - the
datecolumn should be either a DATE, DATETIME or TIMESTAMP data type rather than string in order to use MySQL Date/Time functionality, and should be using DATE_FORMATif the value will not be a MySQL standard date format
- by 是一个保留关键字，你需要反引号来逃避它在 MySQL 中的使用（见上面的例子）。另一种方法是将列重命名为不是保留字的名称 - 对每一列和/或表名进行反引号会隐藏此类问题而不是学习
- NULL 不应在单引号内，否则会被解释为字符串；
DEFAULT如果列上有 DEFAULT 约束，那将是另一种选择 - 该
date列应该是 DATE、DATETIME 或 TIMESTAMP 数据类型而不是字符串以使用MySQL 日期/时间功能，并且如果该值不是 MySQL 标准日期格式，则应使用DATE_FORMAT
回答by markshep
Try calling mysql_error() to get the error message: http://php.net/manual/en/function.mysql-error.php
尝试调用 mysql_error() 以获取错误消息：http: //php.net/manual/en/function.mysql-error.php
回答by McHerbie
First of all, don't put the single quotes around NULL or else it will be entered as a string.
首先，不要将单引号放在 NULL 周围，否则它将作为字符串输入。
Also, I am assuming that you are using mysql_real_escape_stringto sanitize $title, $body, etc.
另外，我假设您正在使用mysql_real_escape_string来清理 $title、$body 等。
$title = mysql_real_escape_string($title);
$body = mysql_real_escape_string($body);
$date = mysql_real_escape_string($date);
$by = mysql_real_escape_string($by);
As mellamokb pointed out, byand dateare special keywords. It's best practice to put ticks around your columns (and table names). (this will fix your query)
正如 mellamokb 指出的那样，by并且date是特殊的关键字。最好的做法是在列（和表名）周围打勾。（这将解决您的查询）
$query = "INSERT INTO `news` (`id`, `title`, `body`, `date`, `by`) VALUES
(NULL, '" . $title . "', '" . $body . "', '" . $date . "', '" . $by . "');";
if ($insert = mysql_query($query, $connect)) {
// Success!
} else {
echo 'MySQL Error: ' . mysql_error($connect); // this will tell you whats wrong
}
mysql_close($connect);
