Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 8 of 8
  1. #1
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    138 times
    I've got a table that has 2 fields, basically an ID num and a field for data, mySQL database FYI.
    Ex:

    1 blah
    2 blech
    3 burp

    Let's say I delete line 2, then add a new line. It makes it # 4. Is there any way to have it automatically fill in those "gaps" in the numeric order? In otherwords, is there a way to have the next line be # 2, not # 4?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  2.  

  3. #2
    Senior Member filburt1's Avatar
    Join Date
    Jul 2002
    Location
    Maryland, US
    Posts
    11,774
    Member #
    3
    Liked
    21 times
    In MySQL, none that I know of other than dropping the auto_increment and re-adding it. However it's not a good idea to do what you're suggesting to avoid relations in tables getting confused.
    filburt1, Web Design Forums.net founder
    Site of the Month contest: submit your site or vote for the winner!

  4. #3
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    138 times
    I don't plan on doing it when it's all up and running. It's just that when I entered in some of the info, an entry or 2 was duplicated, and so when I deleted the extra ones, and then added more entries, the numbers are off. There's no way that I'll do that when the whole thing is up and running.

    Ok, I just took off the auto-increment and added a new entry, which defaulted to 0. Updated the ID num to fill in one of the gaps, then turned auto-increment back on and added a new field. It resumed incrementing from where it left off.

    I take it there's an internal counter somewhere within the mySQL database?
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  5. #4
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    I'm not sure about MySQL, but in Microsoft SQL Server, you can tell your database to allow you to insert into an auto-increment field like so:
    Code:
    SET IDENTITY_INSERT table_name ON;
    
    INSERT INTO table_name (identity_field, field2, field3)
    VALUES (1, value, value);
    
    SET IDENTITY_INSERT table_name OFF;
    This will force an insert with an identity_field value of 1, even if it's an autonumber.

  6. #5
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    its not quite so much about internal counters but about table relations, like it has been said beforehand, if u have a customers table, delete an id of 5 then put the 6th id in its place to keep it tidy, other relations will pull up different details, just like the forums, if a member is deleted, their id cannot (shouuldn't) be used for a new account, causes loads of crap to happen !

  7. #6
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    138 times
    As I have said, I do NOT plan on doing this once the whole database is set up, I'm just entering in basic info for menu fields and messed up one part. I can just wipe it and start from scratch, but was wondering if there was a way around this, for 2 reasons.

    1. I'm lazy
    2. To increase my database knowledge

    Transio, do you know if that still auto-increments the counter behind the scenes? In otherwords, let's say the last one entered was # 16. I use that command to fill in a gap, then enter in another one normally. Will it be # 17 or # 18? Either way, I'll look it up to see if it's supported in mySQL and try it.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  8. #7
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    It does not increment the counter.

    If you're just looking to wipe your table clean, you might want to create a DDL script... something like this:
    Code:
    DROP TABLE my_table;
    
    CREATE TABLE my_table (
        id INT NOT NULL AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        KEY pk_my_table (id)
    );
    
    INSERT INTO my_table (name) VALUES ('data 1');
    INSERT INTO my_table (name) VALUES ('data 2');
    INSERT INTO my_table (name) VALUES ('data 3');
    INSERT INTO my_table (name) VALUES ('data 4');
    Then, each time you want to alter your initialization values, just modify the script and execute the whole thing.... this will create the table from scratch. :-P

  9. #8
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,657
    Member #
    1234
    Liked
    138 times
    Nah, the mySQL control panel has a rt click command that clears the data, but keeps the structure of the table. ATM I'm looking into any free db progs out there that show you relationships in a mySQL database, akin to Access, or a way to have Access do it. Transio, mind checking out the 2nd to last post on the following thread? You may be able to answer it.
    http://webdesignforums.net/thread6490.html
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 12:40 PM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com