Get SCOPE_IDENTITY() in PHP

Question

Been trying to get the SCOPE_IDENTITY() (last ID inserted into DB) and store it as a variable in my PHP function.

Looked at all the answers I could possibly find on stackoverflow and I've still not managed to get there.

This is what I currently have:

// Confirm booking (update database) function
public function insert_userPost($conn) 
{
    // SQL INSERT command
    $sql = ("
           INSERT INTO userPost (content, submitted, emotion)
           VALUES ('$this->post', 'NOW()', '$this->emotion');
           ");
    if ($conn->query($sql) === TRUE) 
    {
        //echo "success";
        $sql = ("SELECT SCOPE_IDENTITY() as Id");
        $result = $conn->query($sql);
        echo $result;
        //header('Location: feed.php?filter=all&page=1');
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

What have I done wrong?

Edit:

Also... in the construct I'm trying to pass the Id ($this->Id) in $this->post but it returns 0. I can see that this is due to the fact that I'm only setting $this->Id after the query goes through, thus returning 0 but I'm unsure how to proceed. Any suggestions?

// Construct
public function __construct($content, $emotion, $conn)
{
    $this->content = $content;
    $this->emotion = $emotion;
    $this->post = 
        "<div id=\'post\'>
            <div id=\'postContent\'>
                <p><b>I\'m $this->emotion because</b> $this->Id $this->content<span class=\'emot\'id=\'$this->emotion\'></span></p>
            </div>
            <div id=\'postInfo\'>
                <span class=\'postRelate\' title=\'Click to relate +1\'><p><b>relate</b> (0)</p></span>
                <span class=\'postSubmitted\'><p>submitted X minutes ago</p></span>
            </div>
        </div>";     
}

// Confirm booking (update database) function
public function insert_userPost($conn) 
{
    // SQL INSERT command
    $sql = ("INSERT INTO userPost (content, submitted, emotion)
             VALUES ('$this->post', NOW(), '$this->emotion')");
    if ($conn->query($sql) === TRUE) 
    {
        //echo "success";
        echo $this->Id = $conn->insert_id;
        //header('Location: feed.php?filter=all&page=1');
    } 
    else 
    {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}

Answer 1

First of all, your question is tagged mysql, but SCOPE_IDENTITY() is a SQL Server function. That being said, your code contains $conn->error, so I'm assuming that you're using MySQL with the MySQLi extension.

The MySQL equivalent to SQL Server's SCOPE_IDENTITY() is LAST_INSERT_ID(). But calling it requires an extra query, which is cumbersome and slower.

Instead, your are advised to use the built-in MySQLi feature for this, the $insert_id property of your connection instance:

$id = $conn->insert_id;

Most SQL libraries have a built-in feature for this. If you were using PDO as your database abstraction layer, you could similarly use PDO::lastInsertId():

$id = $pdo->lastInsertId();

And this would work for both SQL Server and MySQL (and others).

Answer 2

If you're using MySQL then you need to use LAST_INSERT_ID(), which is the SQLServer equivalent.

$sql = "SELECT LAST_INSERT_ID() as Id";

You can also use the php property $conn->insert_id