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);
}
?>

Does this answer your question? [Get SCOPE\_IDENTITY() in PHP](https://stackoverflow.com/questions/34009259/get-scope-identity-in-php) — SMor, Apr 12 '20 at 16:52
You need to show us your code **with** `scope_identity()` so we can tell you what you are doing wrong. — Dale K, Apr 12 '20 at 20:29
Hi, I tried the changes on the link SMor gave but to no luck - I added this into the edit, thanks — b.man, Apr 13 '20 at 06:46

Zhorov · Accepted Answer · 2020-04-14T13:59:09.660

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);
}
?>

Correct way/location to use Scope_Identity()

1 Answers1

Linked