Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder
    Join Date
    Apr 2006
    Posts
    154
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question Catch the auto-increment ID

    Hello!!

    How can I catch an auto-increment ID in a table with a SQL querie?

    For example:

    ...ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

    and I wan´t select the 20. How can I do this??

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    You don't select it before entering your data you select it afterwards with last_insert_id() and use that before entering data into the next table.

    So if table A has the auto increment, perhaps a customerid column and you are then entering information about the newly created customer in a warehouse table you would do something like:

    Code:
    insert 
    into companytable
    (company_name, address)
    values
    ('G.F. Smith and Sons','London')
    and now you want to retrieve the companyid to insert into the warehouse table do this:

    Code:
    insert into warehouse
    (companyid, warehouselocation)
    values
    (last_insert_id(),'Ilford')

  • #3
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    if you have mysql 5 available, you can find out the auto_increment value this way.
    Code:
    select auto_increment from information_schema.tables where table_name='your_tbl_here'
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    There shouldn't be a reason to rely on the value of what the auto increment number is, you should be using it as in the example above.

  • #5
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    normally, i would agree with you, but I just came across a situation where I needed to find out what the auto_increment value.
    When an insert fails (duplicate key for example), mysql_insert_id returns 0. I needed to get the next number so I could still insert data with out duplicate key errors.
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    I'm not sure how you mean. If the insert failed then obviously you wouldn't be adding values to a second table based on the failure of the data in the first table now would you?

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by eak
    normally, i would agree with you, but I just came across a situation where I needed to find out what the auto_increment value.
    When an insert fails (duplicate key for example), mysql_insert_id returns 0. I needed to get the next number so I could still insert data with out duplicate key errors.
    i also don't understand that situation. when you have an auto_increment column, you will not be specifying a value for that column when you do an insert, so i don't quite see how you can get duplicates.
    + getting the value doesn't mean that this value wount be used by another instance by the time you make the insert

    the only good reason i can think of (to get the next value of an autoincrement) is when you have an rdbm that is able to pre-allocate auto_increment PK values. If the server goes down, then your only option to know what the PK of the next record will be is to request the next auto_increment value.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

  • #8
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    Quote Originally Posted by guelphdad
    I'm not sure how you mean. If the insert failed then obviously you wouldn't be adding values to a second table based on the failure of the data in the first table now would you?
    I dont think I explained my example well enough.
    In my situation, I have one table that holds data about screens. This screens table has a screen_id (auto_increment key).
    I needed the option for the user to change the screen_id manually after the screen was created. for example, I create a screen and its id is 1. Then I change its id to 3. I insert another screen (number 2) ok. When I add another screen, which would be 3 due to the auto_increment, it fails due to duplicate keys. In my case, I lookup the auto_increment value after the insert failed and I increase it then try the insert again.
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    then your table design is incorrect. you should not allow an auto incremented column to be changed at all. the only reason you should be using an auto incremented primary key is where there is no suitable candidate for a primary key in the data you already have. your application should not rely on this primary key other than to provide uniqueness for that row. the user should not even know about or need to use this column.

  • #10
    eak
    eak is offline
    Regular Coder eak's Avatar
    Join Date
    Jun 2002
    Location
    Nashville, TN
    Posts
    354
    Thanks
    0
    Thanked 26 Times in 26 Posts
    Quote Originally Posted by guelphdad
    then your table design is incorrect. you should not allow an auto incremented column to be changed at all. the only reason you should be using an auto incremented primary key is where there is no suitable candidate for a primary key in the data you already have. your application should not rely on this primary key other than to provide uniqueness for that row. the user should not even know about or need to use this column.

    I understand that. I originally was going to have a screen_id (auto_increment key) and screen_number (int) so the user could still change the screen_number.

    The only reason I did it the way I did was to see if it could be done with MySQL using 1 column instead of 2. It was more of an experiment than anything else.
    eak | "Doing a good deed is like wetting your pants; every one can see the results, but only you can feel the warmth."

  • #11
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by eak
    I needed the option for the user to change the screen_id manually after the screen was created. for example, I create a screen and its id is 1. Then I change its id to 3. I insert another screen (number 2) ok. When I add another screen, which would be 3 due to the auto_increment, it fails due to duplicate keys. In my case, I lookup the auto_increment value after the insert failed and I increase it then try the insert again.
    Quote Originally Posted by eak
    I understand that. I originally was going to have a screen_id (auto_increment key) and screen_number (int) so the user could still change the screen_number.

    The only reason I did it the way I did was to see if it could be done with MySQL using 1 column instead of 2. It was more of an experiment than anything else.
    so basically, you are just wasting our time here?

    you want an auto_increment that shouldn't automatically increment and which values you want to update after the records are created, and where you fill up the gaps to keep a continuous serie?
    What's the experiment here: these can all be done with a few simple querys and recordset processing? and what has this actually got to do with auto_increments or even getting the next value of the auto_increment? Why not simply get the highest value like
    select max(colname) from tablename
    which you then increment for your insert?
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


  •  

    Posting Permissions

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