phpsql-serversqlsrvscope-identity

Correct way/location to use Scope_Identity()


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

Solution

  • You need to fix some issues in your code:

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