I have a MVC framework that I'm using. I'm trying to insert a customer to the DB and return its ID at the same time.
I've reviewed the posts below and tried what they say but I still can't seem to get it to work properly. The best I've been able to get is having the last lastInsertId() to return null.
SQL Server - Return value after INSERT
This is part of my Database.php library so you understand what I'm doing in the model (The last function is where I call the lastInsertId()):
<?php
class Database {
private $dbHost = DB_HOST;
private $dbUser = DB_USER;
private $dbPass = DB_PASS;
private $dbName = DB_NAME;
private $statement;
private $dbHandler;
private $error;
public function __construct() {
$conn = 'mysql:host=' . $this->dbHost . ';dbname=' . $this->dbName;
$options = array(
PDO::ATTR_PERSISTENT => true,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
try {
$this->dbHandler = new PDO($conn, $this->dbUser, $this->dbPass, $options);
} catch (PDOException $e) {
$this->error = $e->getMessage();
echo $this->error;
}
$this->conn = $conn;
}
//Allows us to write queries
public function query($sql) {
$this->statement = $this->dbHandler->prepare($sql);
}
//Bind values
public function bind($parameter, $value, $type = null) {
switch (is_null($type)) {
case is_int($value):
$type = PDO::PARAM_INT;
break;
case is_bool($value):
$type = PDO::PARAM_BOOL;
break;
case is_null($value):
$type = PDO::PARAM_NULL;
break;
default:
$type = PDO::PARAM_STR;
}
$this->statement->bindValue($parameter, $value, $type);
}
//Execute the prepared statement
public function execute() {
return $this->statement->execute();
}
//Execute and return last ID
public function exAndReturn() {
$this->statement->execute();
return $this->conn->lastInsertId();
}
And here is the model where I query the DB and attempt to return the ID.
public function createAndReturnCustomer($data) {
$this->db->query('INSERT INTO customer(first_name, last_name, phone_number_1, phone_number_2, phone_opt_out, email_1, email_2, email_opt_out, referral_type_id, referral_detail, company_name, company_as_customer, re_agent, created_date
) VALUES (:first_name, :last_name, :phone_number_1, :phone_number_2, :phone_opt_out, :email_1, :email_2, :email_opt_out, :referral_type_id, :referral_detail, :company_name, :company_as_customer, :re_agent, CURRENT_TIMESTAMP
)');
$this->db->bind(':first_name', $data->first_name);
$this->db->bind(':last_name', $data->last_name);
$this->db->bind(':phone_number_1', $data->phone_number_1);
$this->db->bind(':phone_number_2', $data->phone_number_2);
$this->db->bind(':phone_opt_out', $data->phone_opt_out);
$this->db->bind(':email_1', $data->email_1);
$this->db->bind(':email_2', $data->email_2);
$this->db->bind(':email_opt_out', $data->email_opt_out);
$this->db->bind(':referral_type_id', $data->referral_type);
$this->db->bind(':referral_detail', $data->referral_detail);
$this->db->bind(':company_name', $data->company_name);
$this->db->bind(':company_as_customer', $data->company_as_customer);
$this->db->bind(':re_agent', $data->re_agent);
return $this->db->exAndReturn();
}
As of right now it's returning:
Fatal Error: Uncaught Error: Call to a member function lastInsertId() on string in ...
I understand that I can write another function to force retrieve the last inserted entry in the DB, but I wanted to do this correctly to avoid any future issues that may happen with expansion and multiple users potentially performing the same actions simultaneously.
