Unknown SQL syntax error

Question

I have sent the following SQL code and received the following error, but am not sure what is wrong with my syntax. Does anyone have any idea?

$sql="CREATE TABLE some(
answer_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
answer_time INT(10) UNSIGNED,
user_id INT(10) UNSIGNED,
option INT(1) UNSIGNED,
gender VARCHAR(6),
age INT(3) UNSIGNED
)";
$result=$conn->query($sql);

if($result){
    echo "success";
}else{
echo $conn->error;
exit;
}

and the error was :

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 'option INT(1), gender VARCHAR(6), age INT(3) )' at line 5

option is a reserved word, and thereby a poor choice as column identifier. — Strawberry, Sep 21 '15 at 16:11

score 2 · Answer 1 · edited Sep 21 '15 at 16:08

2

Word OPTION is reserved in MySQL https://dev.mysql.com/doc/refman/5.5/en/keywords.html

If you're planning to use keywords and reserved words as column and table names you have to back tick them:

CREATE TABLE some(
    answer_id INT(10) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    answer_time INT(10) UNSIGNED,
    user_id INT(10) UNSIGNED,
    `option` INT(1) UNSIGNED,
    gender VARCHAR(6),
    age INT(3) UNSIGNED
)

edited Sep 21 '15 at 16:08

Jay Blanchard

34,243
16
77
119

answered Sep 21 '15 at 16:03

Ostin

1,511
1
12
25

1

To be perfectly honest and consistent you should back tick ***all*** column names. (It is *not* escaping.) – Jay Blanchard Sep 21 '15 at 16:04

Unknown SQL syntax error

1 Answers1