Unfortunately I've been asked to write an app that runs off a MSSQL server for the first time. Now, nearly all of the essential functions are the same as mysql, but I can't think of a substitute for mysql_insert_id

I could just run another database query on the information I just input and pull out the new ID, but is there an easier / more efficient way?

Thanks,

-Aaron

    SELECT @@IDENTITY as ID

    Trick is you need to do this on the same exact connection you did the insert on. For PHP code, take a look here:

    http://www.php.net/function.mssql-query
    and check out "jesse at lostangel dot net" comment...

      Thanks astroteg, but I guess I don't know what SELECT @@IDENTITY means...maybe I just haven't seen it around. Could you please explain how to use it? Thanks.

        @@IDENTITY is a MS SQL internal variable. MS SQL will automaticallly populate this variable with the auto inserted ID. Trick is you have to select it while it remembers this variable (otherwise it will no longer be available until your next insert with an auto id).

        I'd recommend following the suggestion found on php.net regarding using exec and then your insert query and then select @@identity. Its a bit easier if you just create an insert only function which automatically retrieves the auto id. Retrieving the @@identity value is as simple as treating it like a select query.

        See about getting your hands on the MSDN for SQL Server (I think its available somewhere on msdn.microsoft.com but don't quote me). It'll be able to go into a lot more details on how MS SQL wants you to interact with these types of variables...

          Write a Reply...