Getting the id of the last inserted record from an MSSQL table using PDO and PHP

Question

I am trying to get the id of the last record inserted in an mssql database using pdo via php. I HAVE read many posts, but still can't get this simple example to work, so I am turning to you. Many of the previous answers only give the SQL code, but don't explain how to incorporate that into the PHP. I honestly don't think this is a duplicate. The basic insert code is:

$CustID = "a123";
$Name="James"
$stmt = "
        INSERT INTO OrderHeader (
            CustID, 
            Name 
        ) VALUES (
            :CustID, 
            :Name
        )";
        $stmt = $db->prepare( stmt  );
        $stmt->bindParam(':CustID', $CustID);       
        $stmt->bindParam(':Name', $Name);
        $stmt->execute();

I have to use PDO querying an MSSQL database. Unfortunately, the driver does not support the lastinsertid() function with this database. I've read some solutions, but need more help in getting them to work.

One post here suggests using SELECT SCOPE_IDENTITY(), but does not give an example of how incorporate this into the basic insert code above. Another user suggested:

    $temp = $stmt->fetch(PDO::FETCH_ASSOC);

But, that didn't yield any result.

you could always do a query after this -> `$stmt->query('SELECT MAX(id) as id FROM OrderHeader')` or `$stmt->query('SELECT id FROM OrderHeader ORDER BY id DESC')`. Then `$temp = $stmt->fetch(PDO::FETCH_ASSOC); echo $temp['id']; //last inserted id` — Sean, Apr 17 '15 at 22:27

score 3 · Accepted Answer · edited Jun 20 '20 at 09:12

If your id column is named id you can use OUTPUT for returning the last inserted id value and do something like this:

    $CustID = "a123";
    $Name="James"
    $stmt = "INSERT INTO OrderHeader (CustID, Name) 
             OUTPUT INSERTED.id
             VALUES (:CustID, :Name)";

    $stmt = $db->prepare( stmt  );
    $stmt->bindParam(':CustID', $CustID);       
    $stmt->bindParam(':Name', $Name);
    $stmt->execute();

    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    echo $result["id"]; //This is the last inserted id returned by the insert query

Getting the id of the last inserted record from an MSSQL table using PDO and PHP

1 Answers1

Linked