php - 我无法使用 php 将行插入数据库

Question

这个问题已经有答案了:

Syntax error due to using a reserved word as a table or column name in MySQL (1 个回答)

已关闭 7 年前。

我正在尝试保存到数据库行。但我总是出错。

我的代码是:

function save_activation_key($connection, $username, $key) {
    $date = time();
    $is_used = 0;
    $query = "INSERT INTO account_activation_key (  key, date,  is_used)
    VALUES (    '" . $username . "',"
                . " '" . $date . "',"
                . " '" . $is_used . "')";
    $retval = mysql_query($query, $connection);
    echo $retval;
    $retval = mysql_query( $query, $connection );
    if(! $retval )
    {
      die('Could not enter data: ' . mysql_error());
    }    
}

在 $connection与数据库的连接有效。

数据库结构:

id : int
key: varcha(45)
date: date
is_used: tinyint(1)

当我调用代码时出现错误:

Could not enter data: 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 'key, date, is_used) VALUES (   'uzivatelsky_jmeno', '1459971829', '0')' at line 1

问题出在哪里？

感谢帮助

Answer 1

key 是 MYSQL 保留字，不应该真正用作列名。

MYSQL Reserved Words List can be found here https://dev.mysql.com/doc/refman/5.7/en/keywords.html

但是，如果您将这些列名称括在反引号中，则可以摆脱它。

您还可以像下面这样简化查询字符串连接，这使得调试变得更加容易。

function save_activation_key($connection, $username, $key) {
    $date = time();
    $is_used = 0;
    $query = "INSERT INTO `account_activation_key` 
                     (  `key`, `date`,  `is_used`)
              VALUES ( '$username', '$date', '$is_used')";

    $retval = mysql_query($query, $connection);
    echo $retval;
    $retval = mysql_query( $query, $connection );
    if(! $retval )
    {
      die('Could not enter data: ' . mysql_error());
    }    
}

BIG NOTE

Please dont use the mysql_ database extension, it is deprecated (gone for ever in PHP7) Which means this code will never run when all that is available is PHP7 or greater. Especially if you are just learning PHP, spend your energies learning the PDO or mysqli_ database extensions, and here is some help to decide which to use