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!

Answer 1

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

Answer 2

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

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.

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.

Answer 5

you may be close connection in your class after executing a query. every newbie doing this. nobody knows what for, but everyone does :)