PHP PDO MySql lastInsertId() returning 0

Question

I have the following code which i have extracted and simplified from the site. This code works fine with just a few fields

But i am unable to get lastInsertId() working, it always returns a 0

Please help

<?php 

class Db {
  private $host = "localhost";
  private $user = "root";
  private $pwd = "";
  private $dbName = "cms";

  public function connect() {
      
    $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbName;
    $pdo = new PDO($dsn, $this->user, $this->pwd);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    
    return $pdo;
  }
}


class Jobs extends Db {
    
     public function addJob($job_date_time, $job_type, $job_decs) {
         
        $sql = "INSERT INTO jobs(job_date_time, job_type, job_decs) VALUES (?, ?, ?)";
        
        $stmt = $this->connect()->prepare($sql);
        
        $stmt->execute([$job_date_time, $job_type, $job_decs]);
        
        $lastId = $this->connect()->lastInsertId();
        echo $lastId; 
    
        
      }
}     

    

    $jobs = new Jobs();
    
    $job_date_time      = "2021-11-11T11:40";
    $job_type           = "Test Type";
    $job_desc           = "Test Desc";
  
    $jobs->addJob($job_date_time, $job_type, $job_desc);

    
    

Answer 1

lastInsertId() only returns the last auto-increment id generated if you call that function in the same session the id was generated in.

Every time you call $this->connect() it opens a new connection, thus it's a new session and any state from the previous session (e.g. the last insert id) is reset.

You might have intended $this->connect() to skip creating a new connection if it already has one.

class Db {
  private $host = "localhost";
  private $user = "root";
  private $pwd = "";
  private $dbName = "cms";
  private $pdo;

  public function connect() {

    if (!$this->pdo) {
      $dsn = 'mysql:host=' . $this->host . ';dbname=' . $this->dbName;
      $this->pdo = new PDO($dsn, $this->user, $this->pwd);
      $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }

    return $this->pdo;
  }
}

This opens a new connection the first time you call connect(), but it saves the connection as an object variable. Each subsequent time time you call connect() it just uses the same connection it had saved earlier. The connection stays open for the lifespan of this Db object.