I want a database table with links that are used to generate the navigation on a website. For instance, the text 'Home' will link to 'http://example.com/home' and the text 'Twitter' will link to the Twitter URL, etc. I also wanted to be able to change the order in which the links are presented, hence the order column. I also want to be able to edit the links, that's why I'm using auto_incremented id's.
Now I want order to be unique, so my plan was to get the max of order and just add one. This is the query I'm using, but it will return: Invalid use of group function
INSERT INTO
`links`
(`id`, `order`, `text`, `html_text`, `link`, `html_link`)
VALUES
(NULL, COALESCE((MAX(`order`) + 1), 1), 'text', 'text (html)', 'url', 'url (html)');
My table is like this:
CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order` int(11) NOT NULL,
`text` varchar(31) NOT NULL,
`html_text` varchar(63) NOT NULL,
`link` varchar(127) NOT NULL,
`html_link` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `order` (`order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
How do I get a valid query doing just what I want?
Thanks in advance!
If you want to do it in one shot, you'll have to do an INSERT ... SELECT combination to get the value from the database and insert based on it;
INSERT INTO
`links`
(`id`, `order`, `text`, `html_text`, `link`, `html_link`)
SELECT
NULL, COALESCE((MAX(`order`) + 1), 1), 'text', 'text (html)', 'url', 'url (html)'
FROM `links`;