SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast spleenboy's Avatar
    Join Date
    Nov 2000
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting Next Auto Incremented Number

    I know that there are a lot of discussions about getting the ID of the last inserted record, but does anyone know if there is a way to obtain the next possible ID that would be generated by auto-incrementation?

    Normally, the next ID will just be the highest current ID+1, and this is the way I've been figuring out the next possible ID so far. This fails, however, when the last record has been deleted. (In that case the next auto-incremented ID would be the highest ID +2.)

    Thanks.
    s-p-l-e-e-n-d-i-f-e-r-o-u-s

  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SHOW TABLE STATUS will tell you what the next AUTO_INCREMENT value will be. if you wanted to show it with a PHP script, you could use this:

    PHP Code:
    $r mysql_query("SHOW TABLE STATUS LIKE '$table'");
    $row mysql_fetch_array($r);

    echo 
    "The next AUTO_INCREMENT value is $row[Auto_increment]"
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    SitePoint Enthusiast spleenboy's Avatar
    Join Date
    Nov 2000
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I guess I need to be more specific

    Dr. Pepper,

    Thanks for your reply. PHP/MySQL makes it so easy.

    Unfortunately, I'm developing in ASP and SQL Server. I guess I should have mentioned that. (stupid me.)
    s-p-l-e-e-n-d-i-f-e-r-o-u-s

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why would you need to know this information? If the row doesn't exist yet then the ID is meaningless.

    If you're performing foreign key relationship stuffing, then why not do this?
    Code:
    BEGIN TRAN
    INSERT INTO tbl VALUES( stuff ) 
    
    INSERT INTO your_fk_table VALUES( @@identity )
    COMMIT TRAN
    Or you can roll your own auto incrementing integer instead of using the identity column.

    Or, you can store used IDs in another table like this:
    Code:
    BEGIN TRAN
    INSERT INTO tbl VALUES( stuff )
    
    INSERT INTO ident_table VALUES( @@identity )
    COMMIT TRAN
    
    -- Get our next number
    BEGIN TRAN
    
    DECLARE @next_id INT
    
    SELECT @next_id = MAX( ident_col ) + 1 
      FROM ident_table
    
    -- Do what you want with @next_id
    
    COMMIT TRAN

  5. #5
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry about that spleenboy!

  6. #6
    SitePoint Enthusiast
    Join Date
    Feb 2002
    Location
    Birmingham, UK
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Spleenboy - this also works in SQL Server:

    USE dbname
    GO
    DBCC CHECKIDENT (tablename, NORESEED)
    GO

    For more info, search for "CHECKIDENT" in BOL (Books Online).

    HTH

  7. #7
    SitePoint Enthusiast spleenboy's Avatar
    Join Date
    Nov 2000
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks all. I'll give these a try on Monday and let you know how it works out.

    MattR--I need to know the next ID because I'm using that information to give an associated uploaded file a unique name that relates to its record in the db. Unfortunately, I can't insert the record first and then name the file, which would make my life much simpler.
    s-p-l-e-e-n-d-i-f-e-r-o-u-s


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
  •