What is my error here, shows SQL syntax error? [duplicate]

Question

$upper = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE key='ndz_limit_up'");
$lower = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE key='ndz_limit_down'");

Please help.

Answer 1

Because, key is a MySQL reserved keyword.

http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html

Please use a back tick against the word key.

$upper = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE `key`='ndz_limit_up'");
$lower = $this->db->query("SELECT * FROM " . DB_PREFIX . "setting WHERE `key`='ndz_limit_down'");

The best option to avoid this kind of problem is that:

We should check MySQL reserved keywords while creating DataBase table fields.

And we should make sure that we are not making use of any keyword as our field.

Ways to tackle with this:

1) Use backtick (`) against table and field names.

2) For tables, prepend database name like: databaseName.tableName.

For fields: prepend table name like: tableName.fieldName.

This way, MySQL will interpret that the provided is not a MySQL reserved keyword, rather a database table or field name.