Correct way/location to use Scope_Identity()

Question

I have an auto incrementing ID called deviceID in one of my fields. I was wanting to pass this to a session in php to use later on and was planning on using scope_identity() as I understand that this is the best way to get the current Primary key ID. However anytime I have attempted to use it I have had a error message saying that it is an undefined function. Here is my code so without the scope_identity():

<?php
session_start();
include 'db.php';

$screenWidth = $_POST['screenWidth'];
$screenHeight = $_POST['screenHeight'];
$HandUsed = $_POST['HandUsed'];

$_SESSION["screenWidth"] = $screenWidth;
$_SESSION["screenHeight"] = $screenHeight;

if (isset($_POST['submit'])) { 
    $screenWidth = $_POST['screenWidth'];
    $screenHeight = $_POST['screenHeight'];
    $phoneType = $_POST['phoneName'];
    $HandUsed = $_POST['HandUsed'];
    $_SESSION["HandUsed"] = $HandUsed;
    $_SESSION["phoneName"] = $phoneType;

    echo 'hello';

    $sql = "
       INSERT INTO DeviceInfo (DeviceID, screenWidth, phoneType, screenHeight, HandUsed)
       VALUES ('$screenWidth','$phoneType', '$screenHeight', '$HandUsed')
       SELECT SCOPE_IDENTITY() as DeviceID
    ";
    if (sqlsrv_query($conn, $sql)) {
        echo ($sql);
        echo "New record has been added successfully !";
    } else {
        echo "Error: " . $sql . ":-" . sqlsrv_errors($conn);
    }

    sqlsrv_close($conn);
}
?>

Answer 1

You need to fix some issues in your code:

• The INSERT statement is wrong - you have five columns, but only four values in this statement. I assume, that DeviceID is an identity column, so remove this column from the column list.
• Use parameteres in your statement. Function sqlsrv_query() does both statement preparation and statement execution, and can be used to execute parameterized queries.
• Use SET NOCOUNT ON as first line in your statement to prevent SQL Server from passing the count of rows affected as part of the result set.
• SCOPE_IDENTITY() is used correctly and it should return the expected ID. Of course, depending on the requirements, you may use IDENT_CURRENT().

The following example (based on the code in the question) is a working solution:

<?php
session_start();
include 'db.php';

if (isset($_POST['submit'])) { 
    $screenWidth = $_POST['screenWidth'];
    $phoneType = $_POST['phoneName'];
    $screenHeight = $_POST['screenHeight'];
    $HandUsed = $_POST['HandUsed'];

    $params = array($screenWidth, $phoneType, $screenHeight, $HandUsed);
    $sql = "
        SET NOCOUNT ON
        INSERT INTO DeviceInfo (screenWidth, phoneType, screenHeight, HandUsed)
        VALUES (?, ?, ?, ?)
        SELECT SCOPE_IDENTITY() AS DeviceID
    ";
    $stmt = sqlsrv_query($conn, $sql, $params);
    if ($stmt === false) {
        echo "Error: " . $sql . ": " . print_r(sqlsrv_errors());
        exit;
    }

    echo "New record has been added successfully !";
    while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
        echo $row["DeviceID"];
    }
    sqlsrv_free_stmt($stmt);

    sqlsrv_close($conn);
}
?>