SitePoint Sponsor

User Tag List

Results 1 to 21 of 21

Thread: auto increment

  1. #1
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    auto increment

    Hi, i'm a neebie in this forum and i'm having a problem. i'm using auto increment in my tables. is there anyway of getting that value? thanks in advance

  2. #2
    He's No Good To Me Dead silver trophybronze trophy stymiee's Avatar
    Join Date
    Feb 2003
    Location
    Slave I
    Posts
    23,424
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Welcome to the forums!

    You don't need to get the value. MySQL will automatically increment that field for you everytime you insert a row.

  3. #3
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, but when i delete the last value i have problems.
    I need the value of auto increment to use in other table.when i dont delete i just do max(id)+1 but if i delete the last max(id)+1 is not going to be the id of the next created
    I need the id value to use in another table

  4. #4
    SitePoint Guru mwolfe's Avatar
    Join Date
    Mar 2005
    Posts
    912
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want the value of the autoincrement column, just ask for it.. I have not done a lot with sql (so maybe i'm not familiar with your table structure) but typically i assign an 'id' to each of my tables.. this column i set as "auto_increment" and i make it the primary key. Now, all you have to do is include
    id as one of the fields you select, and that will get you the value of the id.
    example : mysql_query("select id from yourtablename");
    Also, after you do an insert you can check mysql_insert_id($result_resource); to get the id of the insert you just made, where $result_resource was the name of the variable you saved your insert query in..

  5. #5
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for example i have 1,2,3,4,5,6,7,8 ,if i delete the id=8 the next element is going to be 9 (9 is the value in the auto_increment) but i need to know before inserting in this table what is going to be that value, for using in another table.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    don't use max(id), this strategy is not safe

    instead, use mysql_insert_id() or last_insert_id()
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT mysql_insert_id() FROM table?? it doesn't word

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, not FROM table

    see LAST_INSERT_ID() examples on this page -->
    http://dev.mysql.com/doc/mysql/en/in...functions.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Jun 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but i'm using a jsp page to acess mysql database

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    okay, then use SELECT LAST_INSERT_ID() as foo and then reference foo in you jsp page
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi all,
    I've got a question also,
    "LAST_INSERT_ID()" returns the last inserted id of all tables wich has auto-increment,

    (e.g. if we have 2 tables A and B with auto-increment fields and insert into A at first and then insert into B, this function returns B's last inserted id)

    What should we do if we want to get last inserted id of a specific table? (last inserted id of A in my example)

    Thanks in advance

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Chagh
    What should we do if we want to get last inserted id of a specific table? (last inserted id of A in my example)
    issue the SELECT LAST_INSERT_ID() immediately after the insert into table A
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you Rudy,
    but what if i can not do this at the right time?
    i couldn't find any functions in MySQL that returns last inserted id of a specific table, do you know any?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i would be very suspicious of an application that couldn't issue a function call at the right time

    you could always query the row back using the values of the fields that were used to create it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    HardCoder md_irfan_amu's Avatar
    Join Date
    May 2005
    Location
    Asia
    Posts
    215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Chagh
    Thank you Rudy,
    but what if i can not do this at the right time?
    i couldn't find any functions in MySQL that returns last inserted id of a specific table, do you know any?
    mysql_insert_id() just gives the latest value incremented in any table which is auto increment so you can get just after the insertion command of particular table ... autoincrement id
    Irfan
    Find Tutors Easyway to get connect with your tutor in india

  16. #16
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if you're using JDBC, see this page: http://dev.mysql.com/doc/connector/j...e-autoinc.html

  17. #17
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you all,
    i can not change my application 'cause it is working now on SQLServer and i just should move it to MySQL.
    why isn't max(id) safe? i thaught i can use it instead of changing application.
    and if it isn't safe, can i use a trigger to save last inserted id of my desired table in another table(or variable?)so that i can use it when i want?
    (i do not need last_inserted_id of my table right after insertion to it)

    Thanks in advance
    Chagh

  18. #18
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    let's say your page is a sign-up form for your website, and after adding the account it automatically logs you in with your new account.

    so both you and i go to the page, fill out the form, and push submit at nearly the same time. my insert happens first, then yours, then my next query gets max(id)... oh-no!... i am now logged in as you since my query for max(id) returns your id.

    what do you mean by this?
    (i do not need last_inserted_id of my table right after insertion to it)
    in post #5 you said:
    i need to know before inserting in this table what is going to be that value, for using in another table.
    you've just contradicted yourself.

  19. #19
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you longneck,
    so if i want last inserted id of a special table in any scope and any session(like ident_current() in SQLServer) i can use max(id) ?

    Chagh

  20. #20
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    with the implementation of auto_increment in all of mysql's current storage engine implementations, yes. but if mysql changes the behavior or implements a new storage engine, they may reuse auto_increment values.

    you should only use max(id) if you really really really want max(id). (notice i did NOT call this the last inserted id)

    if you want the id of the row previously inserted, you have to use one of the other suggested, or add a datetime column to timestamp insertions and use that.

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    ... or add a datetime column to timestamp insertions and use that.
    but make sure you create and retain the datetime value in your scripting language -- don't use mysql's NOW() because then you're in the same boat as retrieving MAX(id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •