Returning the "Auto-Increment" column value

naresh_singam · #1 (**permalink**) 02-04-09, 05:24

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.

mike_bike_kite · #2 (**permalink**) 02-04-09, 05:55

Code:

select mysql_insert_id()

The documentation is here

Mike

naresh_singam · #3 (**permalink**) 02-04-09, 08:05

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.

mike_bike_kite · #4 (**permalink**) 02-04-09, 08:36

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" );

r937 · #5 (**permalink**) 02-04-09, 13:17

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

Internet Services

Web Development

Digital Marketing

Consumer Tech