CODEWITHSUNDEEP
phpsqlsql-server
Phate
Phate

Reputation: 6612

sqlserver+php: get just inserted row id

I tried this way:

$result = odbc_exec($connection, 'SELECT id FROM MyTable WHERE id = @@Identity');

but it gives me

syntax error: unexpected T_VARIABLE

edit: here's complete logic thanks to your help:

$result = odbc_exec($connection, 'INSERT data into the table;SELECT SCOPE_IDENTITY() AS id;');
$row = odbc_fetch_array($result); //this line gives the error!
$id = $row['id'];

edit2: I missed a ";" =_=

Anyway scope_identity does not work: gives

no tuples available at this index

on fetcharray call

Upvotes: 0

Views: 1486

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

Why aren't you just saying:

$result = obdc_exec('INSERT dbo.MyTable(...) VALUES(...); SELECT id = SCOPE_IDENTITY();');

Or better yet, put the logic into a stored procedure and stop burying ad hoc SQL in your application code.

EDIT because PHP is weird and may treat your INSERT as a resultset, you may need to play with this (pardon me, but I am not a PHP expert):

odbc_next_result($result);

You may also be able to suppress the first result by saying:

SET NOCOUNT ON; INSERT ...; SELECT id = SCOPE_IDENTITY();

Again, I don't know, I don't do PHP. I still think you should be doing this in a stored procedure.

Upvotes: 1

Andomar
Andomar

Reputation: 238078

After inserting a row, you can look up the last inserted id using scope_identity(). There's no need to look it up in the original table:

SELECT SCOPE_IDENTIY()

If the row was inserted on another connection, you can query the maximum value of id:

SELECT max(id) FROM MyTable

Upvotes: 1

Related Questions