lastInsertId returns 0 (zero)

Question

I need to get the ID of my last INSERT in the database, however the function lastInsertId() only returns me 0.

This is my class that connects to the bank:

<?php 
namespace App\model;

class ModelConexao{
    
    public function conecta() {
        
        try {

            $pdo = new \PDO("mysql:host=".HOST.";dbname=".DB."","".USER."","".PASS."",array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

            $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

            return $pdo;
            
        } catch (Exception $e) {
            
            echo "Falha ao tentar conectar-se ao banco de dados. Erro: " . $e->getMessage();

            exit();
        }
    }
}
 ?>

This is my class that inserts the data into the database:

<?php 
namespace App\model;

use App\model\ModelConexao;

class ModelCadastro extends ModelConexao {
    
    public function insert($email, $senha){

        $sql = $this->conecta()->prepare("INSERT INTO LOGIN SET email=:email, senha=:senha");

        $sql->bindParam(":email",$email,\PDO::PARAM_STR);

        $sql->bindParam(":senha",$senha,\PDO::PARAM_STR);

        $sql->execute();

        return $this->conecta()->lastInsertId();
    }
}
 ?>

Answer 1

The adopted way is that in each $this->conecta() has a new instance of the class PDO used to abstract connection to a database, after calling this method again to rescue the last id inserted is a new instance of a new connection. Quick Fix:

<?php 
    namespace App\model;

    use App\model\ModelConexao;

    class ModelCadastro extends ModelConexao {    
        public function insert($email, $senha)
        {
            $conn = $this->conecta(); //pega a instância e utiliza no decorrer do código
            $sql = $conn
               ->prepare("INSERT INTO LOGIN SET email=:email, senha=:senha");
            $sql->bindParam(":email",$email,\PDO::PARAM_STR);
            $sql->bindParam(":senha",$senha,\PDO::PARAM_STR);
            $sql->execute();
            return $conn->lastInsertId();
        }
    }
?

the solution in my ideal view is not to make inheritance because to each class that needs to use a connection to the database will be a new instance, usually the ideal is always work with only one connection in each request, improved performance and resource savings of the database.

---

I would use injection and not inheritance, decrease coupling and cause the connection class to be shared in all classes that so need operation with the database, example:

<?php 
    namespace App\model;

    class ModelConexao {
        private $pdo;
        public function __construct()
        {
            $this->conecta();
        }
        
        public function getConnection()
        {
            return $this->pdo;
        }
        
        private function conecta() 
        {           
            try {

                $this->pdo = new \PDO(
                    "mysql:host=".HOST.";dbname=".DB."","".USER."","".PASS."",
                    array(\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
                $this->pdo->setAttribute(
                    \PDO::ATTR_ERRMODE,
                    \PDO::ERRMODE_EXCEPTION
                );
            } catch (Exception $e) {            
                throw $e;
            }
        }
    }
?>

---

<?php 
    namespace App\model;

    use App\model\ModelConexao;

    class ModelCadastro {
            
        private $conn;
        
        public function __construct(PDO $conn)
        {
            $this->conn = $conn;
        }
        
        public function insert($email, $senha)
        {           
            $sql = $this->conn
                ->prepare("INSERT INTO LOGIN SET email=:email,senha=:senha");
            $sql->bindParam(":email",$email,\PDO::PARAM_STR);
            $sql->bindParam(":senha",$senha,\PDO::PARAM_STR);
            $sql->execute();
            return $this->conn->lastInsertId();
        }
    }
?>

Using:

$conexao = new ModelConexao();
$cadastro = new ModelCadastro($conexao->getConnection());
$cadastro1 = new ModelCadastro1($conexao->getConnection());