Problem getting inserted ID via PDO

1

I am having a bit of a problem for getting the last id for my insert, i have a customer_id field and its an auto increment in mysql but somehow i cant get the id when i insert the new record.

if i use return $this->db->lastInsertId(); i get an "0" if i use return $sth->lastInsertId(); i get Call to a member function lastInsertId() on null

below is my code

if(self::valNewCustomer($data) == false) {
        $sth = $this->db->prepare('CALL `vodex_oss`.`sp_customer_insert`(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)');
        $sth->bindParam(1, $data->quicken_id, PDO::PARAM_STR, 20);
        $sth->bindParam(2, $data->old_id, PDO::PARAM_STR, 25);
        $sth->bindParam(3, $data->status, PDO::PARAM_STR, 10);
        $sth->bindParam(4, $data->name, PDO::PARAM_STR, 60);
        $sth->bindParam(5, $data->dba, PDO::PARAM_STR, 60);
        $sth->bindParam(6, $data->address_line_1, PDO::PARAM_STR, 60);
        $sth->bindParam(7, $data->address_line_2, PDO::PARAM_STR, 60);
        $sth->bindParam(8, $data->address_line_3, PDO::PARAM_STR, 60);
        $sth->bindParam(9, $data->city, PDO::PARAM_STR, 60);
        $sth->bindParam(10, $data->state, PDO::PARAM_STR, 20);
        $sth->bindParam(11, $data->zip, PDO::PARAM_STR, 10);
        $sth->bindParam(12, $data->country, PDO::PARAM_STR, 10);
        $sth->bindParam(13, $data->owner, PDO::PARAM_STR, 10);
        $sth->bindParam(14, $data->sales_rep, PDO::PARAM_STR, 20);
        $sth->bindParam(15, $data->phone, PDO::PARAM_STR, 20);
        $sth->bindParam(16, $data->fax, PDO::PARAM_STR, 20);
        $sth->bindParam(17, $data->web, PDO::PARAM_STR, 200);
        $sth->bindParam(18, $data->customer_since, PDO::PARAM_INT);
        $sth->bindParam(19, $data->created_on, PDO::PARAM_INT);
        $sth->bindParam(20, $data->created_by, PDO::PARAM_STR, 20);
        $sth->bindParam(21, $data->updated_on, PDO::PARAM_INT);
        $sth->bindParam(22, $data->updated_by, PDO::PARAM_STR, 20);
        $sth->execute();

        return $this->db->lastInsertId();
        
    }
2

When you call a stored procedure the PDO lastInsertId() method always returns an empty result .

Try as last command in your procedure:

SELECT LAST_INSERT_ID();

The ID comes back like a SELECT result.

3

Ok, so how would i then access the return value via PDO in Slim ?

4

PDO has nothing to do with Slim. Slim does not pretend how you should use PDO. You might as well use another database library.

The question is, why do you need a stored procedure? You can also use a normal INSERT statement.

Example:

$row = [
    'username' => 'bob',
    'email' => 'bob@example.com'
];
$sql = "INSERT INTO users SET username=:username, email=:email;";
$status = $pdo->prepare($sql)->execute($row);

if ($status) {
    $newId = $pdo->lastInsertId();
}

PS: What do you get if you call this?

$row = $pdo->query('SELECT LAST_INSERT_ID() AS id')->fetch();
print_r($row);
5

I actually got it fixed with some help.

in the stored Procedure i added an
OUT id INT(11) //Parameter
Then at end of the Insert statement I
SET id = LAST_INSERT_ID();
After i inserted i call
$id = $this->db->query(“SELECT @id”)->fetchColumn();

That gets me the Newly created ID from the Insert