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!

score 5 · Accepted Answer · answered Aug 06 '10 at 16:14

5

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

answered Aug 06 '10 at 16:14

Sebs

2,978
1
15
10

Yep, you're correct. I'm using PDO and it has a `lastInsertId()` method! I didn't think of that. Thanks! – Mike Moore Aug 06 '10 at 16:19

score 2 · Answer 2 · answered Aug 06 '10 at 16:13

2

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

answered Aug 06 '10 at 16:13

David

2,821
20
16

score 0 · Answer 3 · answered Apr 27 '15 at 17:50

0

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

answered Apr 27 '15 at 17:50

user596387

23
3

This should've been a comment, not an answer. Although `mysql_insert_id` is depreciated, `mysqli_insert_id` is not. – A Friend Jun 08 '20 at 13:13

score 0 · Answer 4 · answered Jun 15 '15 at 13:21

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

score 0 · Answer 5 · answered Aug 06 '10 at 16:15

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.

score 0 · Answer 6 · answered Aug 06 '10 at 16:15

0

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.

answered Aug 06 '10 at 16:15

Pablo Santa Cruz

176,835
32
241
292

score 0 · Answer 7 · edited Jan 07 '16 at 11:13

0

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

edited Jan 07 '16 at 11:13

Panda

1,231
18
27

answered Jan 07 '16 at 10:13

saeed

11
2

Using LAST_INSERT_ID() via PHP?

7 Answers7

Linked