MySQL Insertion Problem

[cpellizzi]

I am having a small problem with inserting data into a database, but it makes all the difference in the program I wrote running.

When I run the following queries, "root" needs to have an id of 0, but it always comes out as 2 after I run the queries. Does anyone know why this is happening? Am I missing something?

CREATE TABLE `categories` (
  `id` int(21) NOT NULL auto_increment,
  `name` varchar(32) NOT NULL,
  `parent` int(21) default '0',
  `lft` int(10) NOT NULL,
  `rgt` int(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;

INSERT INTO `categories` VALUES (0, 'root', NULL, 1, 2);
INSERT INTO `categories` VALUES (1, 'New category', 0, 1, 1);

[Dan Grossman]

The default behavior of an AUTO_INCREMENT column is to generate and use the next autoincrement value when you specify NULL or 0 as a value for that column.

[cpellizzi]

Thanks for the reply. How might I override that? Should I just make the autoincrement column start at 0?

[Dan Grossman]

What's the difference if the root is at 0 or 1? Can't you adjust your algorithm to use 1 or look for root? I can't see why you would need the ID to be any specific value unless you're using some sort of closed-source system you can't modify. The rest of whatever tree you're storing would use the parent/left/right columns in the row, not the generated ID.

[cpellizzi]

Well, that category is never shown. I referred to all categories by the ID throughout the program and really do not want to go back and change all of the code. All I need to do is make the root category id 0.

[cpellizzi]

Nevermind, I have changed all 0 references to 1.