SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to Change Next Autoindex ID Number in phpMyAdmin?

    Can somebody tell me how I can change the Next Autoindex ID number for a row in phpMyAdmin (latest version)? Say I have 50 rows of information, then I delete 45 of them. How can I make it so it will start the next ID number in the table to 6 instead of 51?
    John Saunders

  2. #2
    SitePoint Evangelist
    Join Date
    Nov 2001
    Location
    UK
    Posts
    466
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    only way is to pull all the info out (except the id) into a comma seperated values file (or any type of file really), delete the table, rebuild the table and re-insert the data.

    there is a reason why mysql never uses the lowest available number in auto_increment fields. It stops hard coded pages accidentally linking to a new item from an old link.

    Why would you want to use the missing numbers?
    If it is because of any reason other than database tidyness, you may want to rethink your schema.
    teckis - that's news to me.

  3. #3
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I basically just wanted to "reset it" because I had added the first 50 rows for testing.

    Thanks for your help. I'll just leave it the way it is.
    John Saunders

  4. #4
    SitePoint Evangelist
    Join Date
    Oct 2001
    Location
    Texas
    Posts
    598
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this

    PHP Code:
    ALTER TABLE table_name AUTO_INCREMENT=51 
    or whatever number you want to reset it and start at

  5. #5
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried this "ALTER TABLE review AUTO_INCREMENT=1" and it said this:

    Error

    SQL-query : ALTER TABLE reviewAUTO_INCREMENT = 1

    MySQL said: You have an error in your SQL syntax near '= 1' at line 1


    Any ideas?
    John Saunders

  6. #6
    SitePoint Evangelist
    Join Date
    Oct 2001
    Location
    Texas
    Posts
    598
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    *maybe* it has to do with the spaces in the string???

    PHP Code:
    ALTER TABLE review AUTO_INCREMENT=

  7. #7
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When I paste "ALTER TABLE review AUTO_INCREMENT=1" it says "ALTER TABLE reviewAUTO_INCREMENT = 1" even though I had the space after review.
    John Saunders

  8. #8
    SitePoint Evangelist
    Join Date
    Oct 2001
    Location
    Texas
    Posts
    598
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try it with quotes
    PHP Code:
    ALTER TABLE `reviewAUTO_INCREMENT=
    not sure if those are quotes or what but thats what i use in my myphpadmin.

  9. #9
    Git-R-Done
    Join Date
    Nov 2001
    Posts
    1,194
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK...I feel dumb, but I didn't think the SQL link I clicked on would make a difference. I went to the table then clicked SQL which wasn't working. When I clicked on the main database name then clicked SQL, it worked.

    Thanks for all of your help!
    John Saunders

  10. #10
    SitePoint Enthusiast
    Join Date
    Jan 2003
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    bug in phpmyadmin

    This is a known bug in phpmyadmin (amoung many, many others) The stupid query parser is screwing up the SQL statement. I tried it in phpmyadmin as well with no luck. Use the commandline or write a php script to execute it if you have no shell access.


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
  •