I have a MySql products table (accessed via PHP and PDO) with the following structure:
CREATE TABLE IF NOT EXISTS `products` (
`id` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`description` text NOT NULL,
`order` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Products are shown via order field. When I remove a product, I would like to decrement order values for all the following rows (i.e. if I remove product with order=2, I want to update the following row from order=3 to order=2, the next from order=4 to order=3, and so on...).
I obviously would like to do this query in the most efficient way with PDO. I've found this question and tried the following statement in PhpMyAdmin:
UPDATE products SET order = order - 1 WHERE (order>2)
but it gives me the following 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 'order = order - 1 WHERE (order>2)' at line 1
What am I missing? And then, what is the correct way to write it with PDO?
ORDER is the reserved word. Use backticks to escape it:
UPDATE `products` SET `order` = `order` - 1 WHERE (`order` > 2);
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments