SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Insertion Problem

    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?

    Code:
    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);

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the reply. How might I override that? Should I just make the autoincrement column start at 0?

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #5
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Addict
    Join Date
    Nov 2005
    Posts
    241
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nevermind, I have changed all 0 references to 1.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •