How to get the id for the last inserted item in SQL Php admin

Question

I tried to use the following code:

$last_id = Connection::connect() -> lastInsertId('name');

to get the id for the last inserted item but does not work. Anyone could help identify the issue?

<?php

class Connection{

    static public function connect(){
        $link = new PDO("mysql:host=localhost;dbname=ham", "root", "");
        $link -> exec("set names utf8");
        return $link;
    }
}

static public function mdlAddAccount($tableOne, $dataOne, $tableTwo, $dataTwo){
    $stmtTwo = Connection::connect() ->prepare("INSERT INTO $tableTwo(name, planLevel) VALUES (:name, :planLevel)");
    $stmtTwo -> bindParam(":name", $dataTwo["name"], PDO::PARAM_STR);
    $stmtTwo -> bindParam(":planLevel", $dataTwo["planLevel"], PDO::PARAM_STR);
    $last_id = Connection::connect() -> lastInsertId('name');

    $stmtOne = Connection::connect()->prepare("INSERT INTO $tableOne(name, user, password, profile, status, relatedAccountID) VALUES (:name, :user, :password, :profile, :status, :relatedAccountID)");

    $stmtOne -> bindParam(":name", $dataOne["name"], PDO::PARAM_STR);
    $stmtOne -> bindParam(":user", $dataOne["user"], PDO::PARAM_STR);
    $stmtOne -> bindParam(":password", $dataOne["password"], PDO::PARAM_STR);
    $stmtOne -> bindParam(":profile", $dataOne["profile"], PDO::PARAM_STR);
    $stmtOne -> bindParam(":status", $dataOne["status"], PDO::PARAM_STR);
    $stmtOne -> bindParam(":relatedAccountID", $last_id, PDO::PARAM_STR);

    #$stmt -> bindParam(":photo", $data["photo"], PDO::PARAM_STR)

    if ($stmtOne->execute() && $stmtTwo->execute()) {
        return 'ok';
    } else {
        return 'error';
    }
    
    $stmt -> close();
    $stmt = null;
}

The answer below solves one issue. Another is that you're calling `lastInsertId()` _before_ you've even executed the query. You need to call it _after_, or there won't be an insert id (since you haven't actually inserted anything yet). You need to execute $stmtTwo first, fetch the insert id and then prepare and execute $stmtOne (the variable names seem backwards though) — M. Eriksson, Oct 05 '22 at 23:53
_Side note:_ I'm assuming the column `relatedAccountID` is of type integer? Then you should set it as `PDO::PARAM_INT` instead of `PDO::PARAM_STR`. Make sure you define the correct [data type](https://www.php.net/manual/en/pdo.constants.php). If you are OK with setting all as strings, you might as well make it easier for you and just remove the types (since PDO::PARAM_STR is the default) — M. Eriksson, Oct 06 '22 at 00:02

Barmar · Accepted Answer · 2022-10-06T19:16:46.177

You have to call lastInsertId() on the same connection where you did the insert. This ensures that it returns the ID of the same row that you inserted, not something inserted by another connection around the same time.

So you shouldn't create a new connection every time you call connect(). It should save the connection and reuse it.

class Connection{
    static $conn = null;

    static public function connect(){
        if (self::$conn) {
            return self::$conn;
        }
        $link = new PDO("mysql:host=localhost;dbname=ham", "root", "");
        $link -> exec("set names utf8");
        self::$conn = $link;
        return $link;
    }
}

You need to get the insert ID after executing the INSERT query, not after preparing it. So you can't do both queries in the same if condition.

if ($stmtTwo->execute()) {
    $last_id = Connection::connect() -> lastInsertId('name');
    if ($stmtOne->execute()) {
        return 'ok';
    } else {
        return 'error';
    }
} else {
    return 'error';
}

Note that you don't actually need to use lastInsertId() here. This is equivalent to the MySQL built-in function LAST_INSERT_ID(), which you can put into the SQL itself.

$stmtOne = Connection::connect()->prepare("
    INSERT INTO $tableOne(name, user, password, profile, status, relatedAccountID) VALUES 
    (:name, :user, :password, :profile, :status, LAST_INSERT_ID())");

Then you need to execute the queries in the proper order. && evaluates its arguments left-to-right, so $stmtTwo->execute() has to be first (your variable names are backwards).

    if ($stmtTwo->execute() && $stmtOne->execute()) {
        return 'ok';
    } else {
        return 'error';
    }

That's a great hint from @Barmar. Also, call `lastInsertId()` without param `'name'`, and do it just after execute the insert. — José Carlos PHP, Oct 06 '22 at 00:07

How to get the id for the last inserted item in SQL Php admin

1 Answers1

Linked

Related