CODEWITHSUNDEEP
phpsqlsql-serverdatabasepdo
riddley walker
riddley walker

Reputation: 65

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

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.

Upvotes: 2

Views: 1881

Answers (1)

Adrian Cid Almaguer
Adrian Cid Almaguer

Reputation: 7791

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

Read more at:

https://msdn.microsoft.com/en-us/library/ms177564.aspx

http://php.net/manual/es/pdo.lastinsertid.php

Upvotes: 3

Related Questions