Mysql Where clause not fetching data

Question

guage_names is varchar incharge_id is integer table has a column option_id which is unique,primary and autoincreament.

There is a row in the table with incharge_id 86

But the query is failing.

Error Number: 1064

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 WHERE incharge_id = 260' at line 1

SELECT guage_names FROM option WHERE incharge_id = 86

Answer 1

Use this instead:

SELECT guage_names FROM `option` WHERE incharge_id = 86

option is a reserved keyword (https://dev.mysql.com/doc/refman/5.6/en/keywords.html)

Answer 2

OPTION is a reserved word. You need to wrap it in backticks.

SELECT guage_names FROM `option` WHERE incharge_id = 86

Pay attention to the special backtick. From the documentation:

The identifier quote character is the backtick (“`”) If the ANSI_QUOTES SQL mode is enabled, it is also permissible to quote identifiers within double quotation marks

You should also consider changing the name of the table. It's bad practice to use reserved words for table/column names as it brings additional complexity as you have seen.

Answer 3

As other people already said, it's a reserved word.

Don't USE it. You should consider RENAMING this table to any other name - since it's not a reserved word, also.

Answer 4

Add backticks for table and column names guage_names if they contain empty spaces,but empty spaces in table names are not OK.