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 requests through PHP, the result is empty.

Under data abstraction, I use a database access class called DB . Here's how I tried these queries using 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 true for me to select a new id using embed_code, since embed_code can be repeated.

Thanks!

Answer 1

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

http://nl.php.net/mysql_insert_id

Answer 2

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

Answer 3

There are special functions for this - you should not use the SQL version, since it does not work.

In the MySQL library, you should use the mysql_insert_id function , while the mysqli object has insert-id . This, of course, can already be shown in any DB class that you use.

Answer 4

You can try using PHP mysql_insert_id .

I think what happens is that every DB::query call is in a different transaction, so $row empty. Perhaps you can read the DB:query documentation to try to run both calls in the same transaction.

Answer 5

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

Answer 6

mysql_insert_id is marked obsolete since PHP 5.5.0.

In this example, uid is the primary key of the video in the table.

 $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']; } 

Answer 7

you must return lastInsertId from the PDO to the outer class of your class. Example:

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