插入新记录后获取UserID

Question

I am currently working with a form where you are able to create new users. You would specify the username, firstname, etc and a new record would be inserted into the Users table in a database.

Right after the creation of an user more steps follow, but for these having the auto generated UserID in the session would be very useful.

How can I accomplish this? I tried the following:

sqlsrv_query($conn1,"INSERT INTO Users (Username, Firstname, Lastname, JobTitle)
VALUES ('$_POST[Username]', '$_POST[Firstname]', '$_POST[Lastname]', '$_POST[JobTitle]'); SELECT SCOPE_IDENTITY() AS UserID;");

$result = sqlsrv_query($conn1); $next_result = sqlsrv_next_result($result); $row = sqlsrv_fetch_array($result);
$_SESSION['UserID'] = $result;

Answer 1

Use OUTPUT INSERTED in your query.

Change Your Code Like,

$result =  sqlsrv_query($conn1,"INSERT INTO Users 
(Username, Firstname, Lastname, JobTitle) OUTPUT INSERTED.UserID 
VALUES ('$_POST[Username]', '$_POST[Firstname]', '$_POST[Lastname]', '$_POST[JobTitle]'); 

and you will get last inserted id in $result