Getting the primary key ID of the last inserted row to run multiple insert operations

Question

I have to run multiple INSERT statements one after the other, but I need to retrieve the primary key of the last inserted row in between, to use in the next INSERT statement. I understand I can do this with SELECT LAST_INSERT_ID();, but I'm not too sure how to use it in my code to store inside the php variable.

function addItem($name, $category, $rating, $user){
    $pdo = Database::connect();
    $sql = "INSERT INTO items (id, category, api, name, api_id) VALUES ('', '". $category ."', '', '". $name ."', '')";
    $pdo->query($sql);
    // $item_id = ???
    $sql = "INSERT INTO ratings (id, user, item, rating) VALUES ('', '". $user ."', '". $item_id ."', '". $rating ."')";
    $pdo->query($sql);
    Database::disconnect();
}

Documentation of the PDO methods shows this: http://php.net/manual/en/pdo.lastinsertid.php — WoogieNoogie, Aug 29 '17 at 19:06
If your primary key column is set to auto increment then no need of adding it in insert query — Ravinder Reddy, Aug 29 '17 at 19:06
try here https://stackoverflow.com/questions/1685860/how-do-i-get-the-last-inserted-id-of-a-mysql-table-in-php — Siddharth Chaudhery, Aug 29 '17 at 19:08

score 1 · Accepted Answer · answered Aug 29 '17 at 19:09

1

You can use PDO's lastInsertId method like this

$item_id = $pdo->lastInsertId();

http://php.net/manual/en/pdo.lastinsertid.php

answered Aug 29 '17 at 19:09

Giedrius

1,370
3
14
37

this works for fields with `auto increment` not `primary key` – S. W. G. Mar 29 '22 at 17:01

score 1 · Answer 2 · answered Aug 29 '17 at 21:17

Try this:

function addItem($name, $category, $rating, $user){
    $pdo = Database::connect();
    $sql = "INSERT INTO items (id, category, api, name, api_id) VALUES ('', '". $category ."', '', '". $name ."', '')";
    $pdo->query($sql);

    // $item_id = ???
    $item_id = $pdo->lastInsertId();

    $sql = "INSERT INTO ratings (id, user, item, rating) VALUES ('', '". $user ."', '". $item_id ."', '". $rating ."')";
    $pdo->query($sql);
    Database::disconnect();
}

Getting the primary key ID of the last inserted row to run multiple insert operations

2 Answers2

Try this: