Why does PDO::lastInsertId return 0?

Question

This is my code.

// insert reward into wallet
$sql = "
INSERT INTO `wallet` (`uid`, `created_at`, `amount`, `type`, `payment_id`) VALUES (:uid, CURRENT_TIMESTAMP, :amount, 'payment', :payment_id);
";
$sth = self::link()->prepare($sql); 
// primary key makes sure payment does not get double rewarded
$sth->execute(
    array(
    ':uid' => $referer,
    ':amount' => $reward,
    ':payment_id' => $payment_data['payment_id'],
    )
);
var_dump(self::link()->errorInfo());
self::log("issuing subscription",self::LOG_LEVEL_DEBUG);
// extend referers subscription
$tid = self::link()->lastInsertId();
var_dump(self::link()->errorInfo());
self::log("using $tid as id for wallet transfer",self::LOG_LEVEL_DEBUG);

My log says:

[2011-07-02 20:31:44] using 0 as id for wallet transfer

However the insert query is successful, the database record is created and both errorInfo outputs give no error.

is any auto_increment field in your table ? lastInsertId works when an auto_increment field exist in your table — Hamid Seyyedi, Jul 02 '11 at 18:45
also you dont need that semicolon in your query... its definitely not required, and i might even make the query fail. Are you sure the the succeeded? — prodigitalson, Jul 02 '11 at 18:48
you nailed it, thank you. if you could post that as an answer so i can upvote and accept. — The Surrican, Jul 02 '11 at 18:51
i am sure i sucesseded but thanks for pointing that out, will remove it — The Surrican, Jul 02 '11 at 18:52
@prodigitalson: Please add your comment as an answer so it can get accepted. — hakre, Jul 16 '11 at 00:35

score 1 · Accepted Answer · edited Jan 03 '13 at 22:58

1

Remove the semicolon or the whitespace after the semicolon or both from your query:

$sql = "
INSERT INTO `wallet` (`uid`, `created_at`, `amount`, `type`, `payment_id`) VALUES (:uid, CURRENT_TIMESTAMP, :amount, 'payment', :payment_id)
";

edited Jan 03 '13 at 22:58

hakre

178,314
47
389
754

answered Jul 16 '11 at 00:50

prodigitalson

58,127
8
92
110

Why does PDO::lastInsertId return 0?

1 Answers1

Linked