How to return the last id inserted in the table?

Asked

Viewed 1,619 times

1

I need to get the generated id, in the last and recent insertion done in a table in SQLSRV, Mssql, using PHP. I’m already connecting to the bank, using the sqlsrv driver.

 $sql = "INSERT INTO [RDO].[dbo].[ANALISE_CRITICA] (CC, NMANALISE, TXTOBS, VLRECEITACONT, VLRECEITABRUTA) 
         VALUES (?,?,?,?,?)";

 $params = array($cc, $analiseCritica, $objetoExtra, $receitaContrato, $receitaBrutaMarco);
 $stmt = @sqlsrv_query( $conn, $sql, $params);
php sql-server

  • http://www.criaweb.com/artigos/181.php ;]

  • Do you already know how to connect to the database and execute queries in SQL Server with PHP? That is, you are only searching the query, or all the way?

  • Specify the driver you are using. If you are mssql or the sqlsrv

  • 1

    Yes, it is connected to the bank. I am using sqlsrv.

  • 2

    Despite the votes to close for this reason, I think the question is clear. What is missing is more details of the current implementation.

3 answers

2

Follow a code to get the last ID generated in SQL Server:

function sql_nextid()
{
    $result_id = @mssql_query('SELECT SCOPE_IDENTITY()');

    if ($result_id)
    {
        if ($row = @mssql_fetch_assoc($result_id))
        {
            @mssql_free_result($result_id);

            return $row['computed'];
        }

        @mssql_free_result($result_id);
    }

    return false;
}

1

In MS Sql Server, the query to return the last ID generated in the session is:

select SCOPE_IDENTITY() as id

See the line I added to your code (last line of the block below):

$sql = "INSERT INTO [RDO].[dbo].[ANALISE_CRITICA] (CC, NMANALISE, TXTOBS, VLRECEITACONT, VLRECEITABRUTA) 
         VALUES (?,?,?,?,?)";

$params = array($cc, $analiseCritica, $objetoExtra, $receitaContrato, $receitaBrutaMarco);
$stmt = @sqlsrv_query( $conn, $sql, $params);

$idRecenGerado = mssql_fetch_assoc(mssql_query("select SCOPE_IDENTITY() as id"));

Here, $idRecenGerado contains the ID generated during insertion in ANALISE_CRITICA.

  • Caffé, I followed your steps and it didn’t work. when I clicked on the save button, this line was displayed "select SCOPE_IDENTITY() as id" and the insertion was not done.

  • $sql = "INSERT INTO [RDO]. [dbo]. [ANALISE_CRITICA] (CC, NMANALISE, TXTOBS, VLRECEITACONT) VALUES (?,? ,? ,? )"; $params = array($cc, $parseCritica, $objectExtra, $receiptContract, $receiptBrutaMarco); $stmt = @sqlsrv_query( $Conn, $sql, $params); "SELECT SCOPE_IDENTITY() AS id"; $query = "INSERT INTO [RDO]. [dbo]. [Parsingdatasignature] (idAnalise, dateAssinatura) VALUES (?,? )";
$idRecenGerado = @mssql_fetch_assoc(mssql_query("select SCOPE_IDENTITY() as id"));
$paramsData = array($idRecenGerado, $dataContrato);
$stmtData = sqlsrv_query($conn, $query, $paramsData);

  • @Gustavosevero The first line of my answer, select SCOPE_IDENTITY() as id, is not part of the code. In it I am just describing how this works in SQL Server. Your code is the second block of my answer.

  • Sorry Caffé, but even taking this line "select SCOPE_IDENTITY() as id", did not work, until locked the page after I clicked save.

  • The key to troubleshooting the problem is the SQL command that rescues the last ID generated in the session, select SCOPE_IDENTITY(). Run this command and get the result by following the same practices you’ve been following to run the other Selects.

1

The driver sqlsrv does not have any native function to recover the id of the inserted record. In this case it is necessary to send two queries at once only the first INSERT and the second one SELECT in scope_identity which will return the value of the entered Identity field.

sqlsrv_next_result returns true if there is something(resultsets, number of affected rows or yet another output) in the preprocessed sql statement that is stored in the variable $stmt. Every call of sqlsrv_next_result() an sql is popped to get its return use sqlsrv_fetch_*

The code should stay that way:

$sql = "INSERT INTO [RDO].[dbo].[ANALISE_CRITICA]
        (CC, NMANALISE, TXTOBS, VLRECEITACONT, VLRECEITABRUTA) 
        VALUES (?,?,?,?,?); SELECT SCOPE_IDENTITY() AS last_id";

$params = array($cc, $analiseCritica, $objetoExtra, 
                $receitaContrato, $receitaBrutaMarco);

$stmt = sqlsrv_query($conn, $sql, $params); //processa a consulta
$next_result = sqlsrv_next_result($stmt); //em caso sucesso desempilha a proxima sql(select) 

if($next_result){
    $item = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC); //obtem o resultado  
    echo $item['last_id'];
}

Note: avoid using @ in the code they mask the error messages and make it difficult to detect the problem, handle the error.

Based on:

Soen

manual - sqlsrv_next_result

  • Lost, the $item did not enter the table.

  • @Gustavosevero, could put the code in the comment

  • if $item empty stem is pq had no query on sqlsrv_next_result($stmt); or the query had an error. @Gustavosevero

Browser other questions tagged php sql-server

You are not signed in. Login or sign up in order to post.