SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,292
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    set the 'Next Autoindex' for my mysql table?

    i've got a mysql database/table and the first column is a smallint, autoincrement primary key.

    how can i get my 'NextÂ_Autoindex' back to 121. it's on 143 now? there are 120 items in the table so the next entry i want to be 120. i used this:
    Code:
    SET INSERT_ID=121;
    and it seemingly worked. in phpMyAdmin it said 121 next to the next autoindex. but when a new entry is made it carries on from 143 !!! arhhhh. how annoying. what's going on there? how can i get the autoindex back to where it should be? thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    johnyboy, the problem isn't getting the autoincrement column to reset

    straight up i will admit i don't know how to do that, but i'm sure you could drop and recreate the table as a last resort

    the problem is wanting avoid gaps in the numbers

    there are many reasons to leave the numbers alone, including some theoretical ones, and they have been discussed here before -- if you search diligently, you might even find those old threads, if you are interested

    could you explain why you need to reset the numbers?

    rudy

  3. #3
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,292
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply rudy

    >but i'm sure you could drop and recreate the table as a last resort

    that's what i've ended up doing, but i've come accross this problem a few times and dropping and recreating seems a bit of a sledgehammer approach. there must be a way to reset/set the 'next autoindex' value?

    the reason i've ended up in this situation is i accidentally added to my table (i'm adding via an automated piece of php code) and in rejigging some things i ended up adding twice. so i deleted the 2nd add and ended up with the auto increment completely out of sync

    >could you explain why you need to reset the numbers?
    my php code that reads from the table relies on the id column being numbered 1,2,3,4,5 etc. so when there's a gap it goes wrong basically. it worked fine and works fine accept when i need to delete lines for any reason, as i've just found out

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    >> "my php code that reads from the table relies on
    >> the id column being numbered 1,2,3,4,5 etc. so
    >> when there's a gap it goes wrong basically"

    then perhaps you need to fix your php code to work with gaps, rather than fix your table to constantly eliminate gaps

    what happens if you delete id 2? are you going to renumber all the ids from 3 up?


    rudy

  5. #5
    SitePoint Wizard
    Join Date
    Apr 2002
    Posts
    2,292
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    i'm never going to want to delete from this table (except if an accident occurs and adds extra like happend just recently)

    ok thanks anyway


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
  •