更新数据库时出现MySQL错误消息[重复]

Question

This question already has an answer here:

• When to use single quotes, double quotes, and backticks in MySQL 12 answers

The following code works just fine.

$q = "UPDATE openwhen SET o_year='$year', o_month='$month', o_date='$date', o_hour='$ohour', c_hour='$chour', combine='$combine' WHERE user_name='$un' LIMIT 1";

$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q
<br />MySQL Error: " . mysqli_error($dbc));

However, when I add one more variable, as shown below, it fails with the indicated error message.

$q = "UPDATE openwhen SET o_year='$year', o_month='$month', o_date='$date', o_hour='$ohour', c_hour='$chour', combine='$combine', match='$mtch' WHERE user_name='$un' LIMIT 1";

$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q
<br />MySQL Error: " . mysqli_error($dbc));

// An error occurred in script '/home/.../update.php' on line 96: Query: UPDATE openwhen SET o_year='2017', o_month='02', o_date='01', o_hour='15', c_hour='22', combine='2017-02-01', match='2' WHERE user_name='Charlie' LIMIT 1
// 
// MySQL Error: 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 'match='2' WHERE user_name='Charlie' LIMIT 1' at line 1

I had added the 'match' field to the database as a ten character VARCHAR, and thought that would work, but either there is something I'm missing in the php code, or something wrong in the database.

</div>

Answer 1

match is a reserved keyword in mysql

you should surround your field names with '`' to indicate that they are column names.

    UPDATE openwhen 
SET    `o_year` = '$year', 
       `o_month` = '$month', 
       `o_date` = '$date', 
       `o_hour` = '$ohour', 
       `c_hour` = '$chour', 
       `combine` = '$combine', 
       `match` = '$mtch' 
WHERE  `user_name` = '$un' 
LIMIT 1