getting the definite last record ID

bholub

This is an easy problem, but for some reason a little hard to explain typed out. So here's what I'm doing (MSSQL - not by choice...):

TableOne has an autonumber field called "ID"

$query1 = insert into TableOne (info) values (info);
$result1 = mssql_query($query2);

$query2 = select ID from TableOne order by ID desc;
$result2 = mssql_query($query2);
$last_id = mssql_fetch_result($result2,0,'ID');

$query3 = insert into TableTwo (fID, info) values ($last_id, info);
$result3 = mssql_query($query3);

The problem here, I believe, is that it's possible (not very... but this has to be definite) that a row could be inserted between $query2 and $query3 which would make the $last_id inaccurate...

Any suggestions would be appreciated.

Thanks!

LordShryku

If you just want the definate last id, use max()

SELECT max(id)
FROM table

AstroTeg

When doing this with ASP, you had to use execute on the query. Then with the connection still open, you do "SELECT @@IDENTITY AS ID" (or as whatever you like). This 2nd query you run will return the ID MS SQL used. Trick is I haven't done this in PHP, but I'm thinking you'll need to use mssql_execute() to do it.

The way you have it will work for 1 user of your app. Any more and you risk the IDs getting thrown off.

bholub

hmmm... thanks AstroTeg, I'll look into it. Once I get it I'll post back here.

koka

any solution for the getting the id or information about the last inserted row after executing the command using the function
mssql_query("INSERT...")

bholub

I found this to work:

$query = "insert into TABLE(stuff) values ('$stuff') SELECT SCOPE_IDENTITY() AS CurrentRowID";
$result = mssql_query($query);
$CurrentRowID = mssql_result($result,'0','CurrentRowID');

HOWEVER, there are pitfalls to this if you use a lot of triggers and stored procedures... because I believe SCOPE_IDENTITY() will give you the LAST active ID for any queries that happen within that call. So if you INSERT query triggers another query containing an identity, you will get THAT identity... not the one from your original INSERT statement. So be careful and test it out!

koka

Thanks! for the reply

I tried this sql
INSERT INTO GROUPS (name, description, parent_group_id) VALUES ('testName', 'testDescription','9') SELECT SCOPE_IDENTITY() AS CurrentRowID

but when i execute this line

$CurrentRowID = mssql_result($result,'0','CurrentRowID');

Warning: mssql_result(): supplied argument is not a valid MS SQL-result resource in

am I using it in a worng way?

bholub

hmmm, not sure. I copy-pasted the actual working code I used (but replaced the table columns and values obviously). the only thing I can think of is that you need to make sure that the table you're selecting from has parent_group_id (or anything) set as an IDENTITY in the db...

sorry i couldn't give a better answer, but it's working for me :bemused:

Roger_Ramjet

Actually this is a non-problem as the manual explains:

http://dev.mysql.com/doc/mysql/en/Information_functions.html

"LAST_INSERT_ID(expr)
Returns the last automatically generated value that was inserted into an AUTO_INCREMENT column. mysql> SELECT LAST_INSERT_ID();
-> 195

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions. "

I would suggest that you read the whole referance as it has much more to say than I have quoted here.