Zend Database Last ID of inserted row. (Using post

Question

Ok, got this query (works fine, inserts an all...)

$db = Zend_Db_Table::getDefaultAdapter();
$data = array(
    "comment_id" => new Zend_Db_Expr("nextval('comments_comment_id_seq')"),
    "comment" => $comment,
    "comment_level" => 1,
    "is_active" => true,
    "status" => 0,
    "id" => $id,
    "user_id" => $_SESSION['role']['user_id'],
    "ts_create" => $created,
    "ts_mod" => $created
);
$db->insert($this->_name, $data);
$newID = $db->lastInsertId();

I have even tried even

$newID = $db->insert($this->_name, $data);

And I can't get the ID's value. Its not mysql(i) so I think thats my first issue, as postgres doesn't appear to have an autoincriment in the way I am used to working with it atleast. So I am hoping someone here can help me out. Anyway to get the "comment_id" columns id, on a new inserted item? Cause right now I try lastInsertID and all I get is false or 1 which in either case is wrong.

Answer 1

Well GordonM pointed me in the direction of a specific document for zend, that reading through that, I was able to find what I needed. Unfortunately no one provided an actual answer. So I am going to answer my own question, for those who may stumble across it when they are stuck in a similar position as I just was.. But will give the up-vote to GordonM for getting me in the right direction. Thanks GordonM

Changing:

$newID = $db->insert($this->_name, $data);

To:

$newID = $db->lastSequenceId('comments_comment_id_seq');

Answer 2

From the Zend documentation, databases that use sequences to generate autoincrements (like Postgres) require you to specify the sequence by name in the lastInsertId() call.

Answer 3

Auto incremented fields are implemented as a sequence in PostgreSQL, the database reads from this sequence when inserting a new value. You have to use the name of that sequence when calling lastInsertId() to get the correct value.