lastInsertId returns 0

Question

Actually, there is a lot of post about this. But even if I checked those, still couldn't find the problem.

DBC class

protected function connect(){

    $dns = "mysql:host=" . $this->server . ";dbname=" . $this->database;
    $pdo = new PDO($dns, $this->username, $this->password);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    return $pdo;
}

and insert function.

public function insert_row($created_at){
    $sql = "INSERT INTO empty_rows (created_at) VALUES (?)";
    $stmt = $this->connect()->prepare($sql);
    $stmt->execute([$created_at]);  
    $id = $this->connect()->lastInsertId();

    echo $id;
}

I think it should be return the last inserted Id but, it just returns 0. what am I missing here?

actually, inserrt is not failing. The row is inserted. I am checking the DB @Barmar — yvl, Apr 15 '20 at 02:02
$this->connect() is not a singleton, pretty sure if you reconnect that value is lost — Lawrence Cherone, Apr 15 '20 at 02:04

Barmar · Accepted Answer · 2020-04-15T02:08:24.797

$lastInsertId() returns the ID that was inserted on the same connection. You get a new connection every time you call $this->connect().

You need to save the result of $this-connect() so you use the same connection.

public function insert_row($created_at){
    $sql = "INSERT INTO empty_rows (created_at) VALUES (?)";
    $connect = $this->connect();
    $stmt = $connect->prepare($sql);
    $stmt->execute([$created_at]);  
    $id = $connect->lastInsertId();

    echo $id;
}

In general you'll slow your application down significantly if you keep creating new connections. I suggest you change the connect() method so it only connects the first time.

protected function connect(){
    if (!isset($this->pdo) {
        $dns = "mysql:host=" . $this->server . ";dbname=" . $this->database;
        $this->$pdo = new PDO($dns, $this->username, $this->password);
        $this->$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    }
    return $this->$pdo;
}

lastInsertId returns 0

1 Answers1

Linked

Related