why lastInsertId is not working, it'll only give an empty value

Question

class Customer extends Dbh {
    protected function setAddCustomer($c_name, $c_address){

      $stmt = $this->connect()->prepare('INSERT INTO  customer (c_name, c_address) VALUES (?, ?);');

      if(!$stmt->execute(array($c_name, $c_address))) {
        $stmt = null;
        header("location:../index.php?error=connectionfaild!");
        exit();      
       }

      $last_id = $this->connect()->lastInsertId();
      return $last_id;
      
    }
}

https://stackoverflow.com/questions/11776818/pdo-lastinsertid-always-return-0 — KHIMAJI VALUKIYA, Dec 18 '21 at 07:29
The one other problem could be using $pdo->exec($sql) instead of $pdo->query($sql). exec($sql) will return always 0 when you use $pdo->lastInsertId(). So use query() instead. — KHIMAJI VALUKIYA, Dec 18 '21 at 07:29
Which database are you using? AFAIK, SQLite doesn't support this functionality for example. — vixducis, Dec 18 '21 at 09:04
What does `connect()` do? Are you opening a new connection with `$this->connect()->lastInsertId();`? — Álvaro González, Dec 18 '21 at 10:12

score 0 · Answer 1 · answered Dec 19 '21 at 11:20

You're establishing a new connection every time you interact with database server within the same script. That has two main side effects:

You're slightly reducing performance and increasing the DB server resource use.
You no longer have access to session-wide features, such as last insert ID or transactions.

The solution is to open one connection and reuse it during script lifetime. You can, for instance, refactor your connect() method into a getConnection() method like:

public function getConnection(): \PDO
{
    if (!$this->connection) {
        $this->connect();
    }
    return $this->connection;
}

... or use some kind of dependency injection:

public function __construct(\PDO $connection)
{
    $this->connection = $connection;
}

If you think about it, it makes sense. A global last inserted ID value wouldn't be particularly useful because concurrent accesses to database server would render it useless for most purposes.

why lastInsertId is not working, it'll only give an empty value

1 Answers1