CODEWITHSUNDEEP
mysqlsqlsyntax
user1574556
user1574556

Reputation: 145

MYSQL syntax error - why is this happening?

I use mysql in my php scripts for more than 6 years but i never encountered error like this.

When i execute this SQL command:

SELECT `discount_items`.* FROM `discount_items` WHERE (position=1) AND (active=1) AND (end<=1344007212) AND (show=1) LIMIT 1

it throws me this error

#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 'show=1) LIMIT 1' at line 1

The table structure is:

CREATE TABLE IF NOT EXISTS `discount_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `image` text CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `discount` float NOT NULL,
  `price1` float NOT NULL,
  `price2` float NOT NULL,
  `bought` int(11) NOT NULL,
  `target` int(11) NOT NULL,
  `desc` text CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `link` text CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL,
  `active` tinyint(1) NOT NULL,
  `start` int(11) NOT NULL,
  `end` int(11) NOT NULL,
  `position` int(11) NOT NULL DEFAULT '1',
  `show` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

I don't get whats wrong. Obviously the 'show' field cause the problem but i already tried everything.. (there must be something wrong with show field because:

SELECT `discount_items`.* FROM `discount_items` WHERE (show=1) AND (active=1) AND (end<=1344007212) AND (position=1) LIMIT 1

throws

#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 'show=1) AND (active=1) AND (end&lt;=1344007212) AND (position=1) LIMIT 1' at line 1

So the problem moves with the show field.

I am sorry if this is common problem but i googled and found nothing. This error is too global and doesn't explain anything to me.

Thanks for any help and tips!

Upvotes: 2

Views: 2118

Answers (3)

Mike Brant
Mike Brant

Reputation: 71384

show is a MySQL reserved word. If you are going to use it to reference a field name, you must use backticks around it like this:

SELECT `discount_items`.*
FROM `discount_items`
WHERE
    (position=1)
    AND (active=1)
    AND (end<=1344007212)
    AND (`show`=1) /* backticks added here */
LIMIT 1

Upvotes: 4

verisimilitude
verisimilitude

Reputation: 5108

show is a MySQL reserved word. Enclose it in backticks to make it work.

Upvotes: 1

John Conde
John Conde

Reputation: 219814

show is a reserved word. Either change it or place it in ticks 

 SELECT `discount_items`.* FROM `discount_items` WHERE (position=1) AND (active=1) AND (end<=1344007212) AND (`show`=1) LIMIT 1

Upvotes: 3

Related Questions