Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2008
    Posts
    7

    Unanswered: Returning the "Auto-Increment" column value

    Hi,

    I have table where one of the column is "Auto-Incremented". Whenever I insert data into that table the query should retuun the value of the Auto-Incremented column. So that I can use that value in my further queries. Please help to achieve this functionality.

    Thanks,
    Naresh.

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Code:
    select mysql_insert_id()
    The documentation is here

    Mike

  3. #3
    Join Date
    Apr 2008
    Posts
    7
    Thanks Mike for your response,

    But my requirement is I should not call the any function or query again. Along with the insert query inself the value should return.

    My actual requirement is :
    I have two tables (parent and child) when ever I call insert method, first I will insert data into the parent table and then I should insert data into the child table where I required the "Auto-Increment" column value to be inserted in the child table.
    If I use function to get the latest id I will have problem when multiple users uses my application.

    Thanks,
    Naresh.

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    You could try something like this :
    Code:
    begin transaction
       insert parent ( id, parent_data ) values ( null, "parent_data" );
       insert child ( id, parent_id, child_data ) values( null, mysql_insert_id(), "child_data" );
    commit transaction
    I don't know anything about your application but I think a function wouldn't be a bad idea for inserting the parent. The function would insert the parent and then return the id of the parent. Each process would use this value when adding it's children. Also if there is little difference between a parent and a child I'd say use the same table and hence the same function to add these as well.

    Code:
    select add_item( null, "parent_data" ) into v_parent_id;
    select add_item( v_parent_id, "child_data_1" );
    select add_item( v_parent_id, "child_data_2" );

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by naresh_singam
    If I use function to get the latest id I will have problem when multiple users uses my application.
    no, this is not true

    mysql keeps track of the last auto_increment value per connection

    use the mysql_insert_id php function if you're using php, otherwise use the LAST_INSERT_ID mysql function
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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