0

I know there are similar questions to this, and I've tried various examples but I seem to be missing something.

I'm using PHP to insert a record in a MS SQL table, code as follows...

 $sql = "Insert into MyTable (column1,column2,column3) VALUES (? , ? , ?);";
 $params = array($value1,$value2,$value3);
 $stmt = sqlsrv_query( $conn, $sql, $params);
 if( $stmt === false ) {
 Echo "Error writing new application to table";
 }

This works fine and my records is added, but i need to grab the 'id' of the last inserted record.

Looking at some of the previous questions on this, I tried several different ways, most recently this...

 $sql = "Insert into MyTable (column1,column2,column3) VALUES (? , ? , ?);";
 $params = array($value1,$value2,$value3);
 $stmt = sqlsrv_query( $conn, $sql, $params);
 sqlsrv_next_result($stmt); 
 sqlsrv_fetch($stmt);
 $lastInsertedId = lastId($stmt);
 echo "RECORD INSERTED WITH ID: " . $lastInsertedId . "<BR>"; 
 if( $stmt === false ) {
 Echo "Error writing new application to table";
 }

but 'lastInsertedId' comes back blank.

When i've done similar things in the past, albeit not in PHP, I've had an additional command on the end of my insert command and used a different function than query, but I cant seem to find any examples of this.

Can anyone point me in the right direction?

Many thanks in advance!

EDIT / SOLUTION:

So i finally got this working... the other examples provided all returned a null value except one which returned an array, so i tried throwing ["id"] at the end of that and boom... there it is!

 $sql = "Insert into MyTable(column1,column2,column3) VALUES (? , ? , ?); SELECT @@IDENTITY as id;";
 $params = array($value1,$value2,$value3);
 $stmt = sqlsrv_query( $conn, $sql, $params);
 $next_result = sqlsrv_next_result($stmt); 
 $row = sqlsrv_fetch_array($stmt); 
 echo "ROW INSERTED WITH ID : " . $row["id"];
 if( $stmt === false ) {
 Echo "Error writing new application to table";
 }

Hopefully this may help someone in future if you come across the same annoyingly simple issue.

John
  • 755
  • 1
  • 18
  • 46
  • A quick search gives this, does it sound familiar? https://stackoverflow.com/questions/5228780/how-to-get-last-inserted-id - seems that you need to retrieve the ID as part of your query on MS-SQL. – droopsnoot Jul 22 '20 at 08:30
  • From my Googling `lastId` is for MySQL, not SQL Server. And @droopsnoot that question is for C#; a completely different language. – Thom A Jul 22 '20 at 08:30
  • @Larnu that's true, I did notice it's a different language, but it seemed to illustrate how it needs to be added to the query and then retrieved, as the OP suggested that they'd done before. Surely when they do the `fetch` this will retrieve the output from the query, which will be the last inserted ID? – droopsnoot Jul 22 '20 at 08:34

1 Answers1

-2

Looking into online documentation it looks like lastId() is not a PHP method. You should try something like mysql_insert_id() which:

Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT).

Unfortunately according to the official documentation this extension was deprecated in PHP 5.5.0, and it was removed in PHP 7.0.0. Instead, the MySQLi or PDO_MySQL extension should be used.

rikyrich
  • 1