Using LAST_INSERT_ID() via PHP?

Question

When I execute the following in the MySQL console, it works fine:

INSERT INTO videos (embed_code) VALUES ('test code here');
SELECT LAST_INSERT_ID();

But, when I execute the above queries via PHP, the result is empty.

Under the data abstraction, I am using a database access class named DB. This is how I have tried the above queries via PHP:

$embedCode = htmlentities($_POST['embed_code']);

//Insert video in database **WORKS**
DB::query("INSERT INTO videos (embed_code) VALUES ('$embedCode');");

//Retrieve id **DOES NOT WORK**
$row = DB::getSingleRow("SELECT LAST_INSERT_ID();");
$videoId = $row[0];

It is not reliable for me to select the new id by the embed_code, as the embed_code could be repeated.

Thanks!

Natasha

Answer 1

Doesn't your database class contain a function to grab the last insert ID? Try mysql_insert_id() otherwise instead.

http://nl.php.net/mysql_insert_id

Selfsupporting

Yep, you're correct. I'm using PDO and it has a lastInsertId() method! I didn't think of that. Thanks! – Natasha

Answer 2

PHP has a function called mysql_insert_id which will give you the same result as the query SELECT LAST_INSERT_ID();.

Larimor

Answer 3

There are dedicated functions for this - you shouldn't be using the SQL version as it doesn't work.

In the MySQL library you should use the mysql_insert_id function, whereas the mysqli object has an insert-id property. This of course may already be exposed in whatever DB class you're using.

Southbound

Answer 4

You can try with PHP's mysql_insert_id function.

I think what's happening is that each DB::query call is in a different transaction, hence, $row is being empty. Maybe you can read DB:query documentation to try running both calls within the same transaction.

Audi

Answer 5

mysql_insert_id is deprecated. So I think this thread needs to be updated.

Godspeed

This should've been a comment, not an answer. Although mysql_insert_id is depreciated, mysqli_insert_id is not. – Moyer

Answer 6

mysql_insert_id is marked as deprecated since PHP 5.5.0.

In this example uid is the primary key of the table videos.

$row = DB::getMultipleRows("SELECT uid FROM videos WHERE uid=LAST_INSERT_ID();");
if (
    isset($row) &&
    is_array($row) &&
    isset($row['0']) &&
    is_array($row['0']) &&
    isset($row['0']['uid'])
) {
    $videoId = $row['0']['uid'];
}

Cousins

Answer 7

you should return lastInsertId of PDO to outer of your class . example:

class DB
{
    public $last_insert_id;
    function x()
    {
        $stmt = $db->prepare($Query);
        $stmt->execute();
        $this->last_insert_id = $db->lastInsertId();
    }    
}

Koster