Getting the last insert id of a record

[Jeewhizz]

Getting the primary key value of the record that was last inserted by a user.

Often, we need to insert a new record in a table with an autonumber variable as primary key, and get the value of the automatically generated primary key back. For instance, when we start a new session for a user, and we need the sessionID to insert as a foreign key into an order table or so.

There are two function that return this value:

mysql_insert_id()--> PHP function
LAST_INSERT_ID()--> MySQL function that can be used by PHP and other Languages or in MySQL command line interface or MySQL front-end

PHP example from php.net:


<?php
mysql_connect("localhost", "mysql_user", "mysql_password") or
die("Could not connect: " . mysql_error());
mysql_select_db("mydb");

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf ("Last inserted record has id %d\n", mysql_insert_id());
?>


MySQL example from mysql.com (in the second insert, the ID is inserted in another table):

INSERT INTO foo (auto,text)VALUES(NULL,'text');
INSERT INTO foo2 (id,text)VALUES(LAST_INSERT_ID(),'text');


Links for more info and comments:

http://www.mysql.com/doc/en/Getting_unique_ID.html
http://www.php.net/mysql_insert_id

Created by: raf

[Morgoth]

<edit>
This post was asking a question about 'LAST_INSERT_ID()' in ASP, but I found out, that I'm an idiot. The code I posted did not work, but it wasn't the MySQL function's fault.
I edited the code for ASP that is using the function so now it works.
You can see what my stupid error was that I did not catch right away.
</edit>
<%
SQL = "INSERT INTO tblA (fld1, fld2) VALUES ('Text', " & Integer1 & ")"
oConn.Execute(SQL)
SQL = "INSERT INTO tblB (id) VALUES (LAST_INSERT_ID())"
oConn.Execute(SQL)
%>

[ranjitcool]

hi
i need some help
please

i have a coloumn in my mysql table

it is called speciality

it already has a value called "good"
now i need to add more values to it without deleteing the present value

for example:- first value = GOOD
after new value inserted

it will be GOOD,OK
and so on!

remember the old value shud not be delted!

please help me
i have 3000 records to be updates liek that

urgent
bye

[cybermantas]

Wrong place I guess...

But this should be of help

SQL="Update <table name> set speciality=speciality+',Ok' "
conn.execute(SQL)

Hope this helps

[odtufatih]

So is there a way of knowing what id you are inserting when you are doing an insert operation ? e.g. I have a picture column and I want to name it with the id of the tuple..For the record with id 5, the column picture will have the value picture5 I mean..to be able to do this I should know the id I am going to insert, but I cannot guess how I can manage this..

I need some help :(

[dur0000]

mysql provides a "show table status" expression to learn various states of our tables. in this case we can learn our next insert id as:
http://www.randomind.com/forum/viewtopic.php?t=6

[bigtiger]

hi, odtufatih

the code written by cybermantas is almost completed code that you can do what you want, but missing that last word that is important:


SQL="Update <table name> set speciality=speciality+',Ok' where id=" & your last id inserted.
( see post above how to get last inserted id)
conn.execute(SQL)

- hope this help