How to return the last id inserted in the table?

1

I need to get the generated id, in the last and recent insertion made 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);
    
asked by anonymous 21.10.2014 / 17:34

3 answers

2

Here is 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;
}
    
21.10.2014 / 18:06
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 .

    
21.10.2014 / 19:28
1

The sqlsrv driver has no native function to retrieve the inserted log id. In this case you need to send two queries at once only INSERT and the second one SELECT at scope_identity which will return the value of the inserted identity field.

sqlsrv_next_result returns true if something exists (resultsets, number of affected rows, or even another output) in the concerned sql statement that is stored in the $stmt variable. At each call of sqlsrv_next_result() a sql is unstacked to get its return use sqlsrv_fetch_*

The code should look like this:

$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, treat the error.

Based on:

p>

manual - sqlsrv_next_result

    
22.10.2014 / 04:50