PDO lastInsertId Empty

[hjg123]

Hi,

I am trying to insert some data into a MySQL database using PDO and then return the ID of the row added. The function is running correctly, data is being inserted correctly, and the first 'test' string is being returned correctly, however nothing is being returned for lastInsertID or the second 'test' string.

There is an ID column in the database that is AUTO_INCREMENT and Primary Key.

Any help appreciated.

public function insertNewCustomer($data){
    $this->db->query("INSERT INTO Customers (First_Name, Surname, Email) VALUES (:firstname, :surname, :email)");
    //Bind Data
    $this->db->bind(':firstname', $data['firstname']);
    $this->db->bind(':surname', $data['surname']);
    $this->db->bind(':email', $data['email']);


//Execute
if($this->db->execute()){
    echo 'test' ; 
    echo $this->db->lastInsertId();
    echo 'test' ;   
} else {
    return false;
}

}

[pbismad]

Do you have php's error_reporting set to E_ALL and display_errors set to ON so that php will help you by reporting and displaying all the errors it detects?

The code have posted is NOT how a prepared query works, even if you have written a (unnecessary) wrapper for the PDO class.

The prepare() method returns a PDOStatement object. You must call the bindParam/bindValue and execute methods of the PDOStatement object, not of the PDO (connection) object.

[hjg123]

Hi,

Thanks for the response. The error I am getting is:

PHP Fatal error: Uncaught Error: Call to undefined method Database::lastInsertId()

I am new to using PDO with PHP so struggling with PDOStatement vs. PDO object.

The full code is:

Thanks again for having a look

<?php 
class Itinerary{
	private $db;

public function __construct(){
	$this->db = new Database;
}

// Insert New Customer Details Into DB
public function insertNewCustomer($data){
	$this->db->query("INSERT INTO Customers (First_Name, Surname, Email) VALUES (:firstname, :surname, :email)");
	//Bind Data
	$this->db->bind(':firstname', $data['firstname']);
	$this->db->bind(':surname', $data['surname']);
	$this->db->bind(':email', $data['email']);


	//Execute
	if($this->db->execute()){
		echo 'test' ; 
		echo $this->db->lastInsertId();
		echo 'test' ;	
	} else {
		return false;
	}


}

}

[pbismad]

There's no good reason to write your own wrapper for the PDO extension, especially if you don't fully understand how to use the PDO extension at all. Just use the PDO and PDOStatment methods and properties directly.

So, either eliminate the Database class and directly use the PDO extension or you will need to add your own lastInsertId() method to the Database class.

Also, your Itinerary class should should not be responsible for creating the database connection. Your main application code should be responsible for creating the database connection, then use dependency injection to supply the connection to any class that needs it.

[hjg123]

Thanks - I was learning from a tutorial that built it in this way so don't want to edit it too much, but have defined lastInsertID in the Database class and now it is working.

Thanks!

[NogDog]

If you do have a reason to create your own "Database" class, it might be best to extend it from PDO so that it inherits all of PDO's methods; then you just layer any additionally needed functionality on top of that.