how to get last inserted Id of a Sqlite database using Zend_Db

Question

I'm trying to fetch the last inserted row Id of a Sqlite DB in my PHP application. I'm using Zend Framework's PDO Sqlite adapter for database handling. the lastInsertId() method is supposed to give me the results, but it wouldn't. In PDO documentation in php.net I read that the lastInsertId() might not work the same on all databases. but wouldn't it work on sqlite at all? I tried overwriting the lastInsertId() method of the adapter by this:

// Zend_Db_Adapter_Pdo_Sqlite
public function lastInsertId() {
    $result = $this->_connection->query('SELECT last_insert_rowid()')->fetch();
    return $result[0];
}

but it does not work either. just returns 0 everytime I call it. is there any special clean way to find the last inserted Id?

Marucci

Answer 1

Given an SQLite3 Database with a table b, as follows:

BEGIN TRANSACTION;
CREATE TABLE b(a integer primary key autoincrement, b varchar(1));
COMMIT;

This code gives me a lastInsertId:

public function lastInsertId() {
    $result = $this->_connection->query('SELECT last_insert_rowid() as last_insert_rowid')->fetch();
    return $result['last_insert_rowid'];
}

That is - if your table is defined correctly, your only problem is likely to be that you tried to fetch key $result[0] - also, whenever you're using a computed column, I recommend aliasing the column using the "AS" keyword as I've demonstrated above. If you don't want to alias the column, in SQLite3 the column should be named "last_insert_rowid()".

Chromatology

it worked well. even the Zend_Db_Adapter_Pdo_Sqlite::lastInsertId() worked. my problem was the definition of autoincrement. thanks. – Marucci

Answer 2

PDO::lastInserId()

see: https://www.php.net/manual/en/pdo.lastinsertid.php

Agostino

Answer 3

Do not use

SELECT * FROM tablename WHERE id = (SELECT COUNT(*) FROM tablename);

instead use

SELECT MAX(id) as id FROM tablename LIMIT 1;

or

SELECT id FROM tablename ORDER DESC LIMIT 1;

Chrysanthemum

Answer 4

SELECT * FROM [tablename] ORDER BY id DESC LIMIT 1

Colocynth

This is considered bad practice in general. A) in order to return the correct result for concurrent access, it must be encapsulated in a serializable transaction or have the table locked, and B) it's an additional (unnecessary) query when the DBMS supports returning the last insert id (in this case it does). – Upstage

Answer 5

Hey, try this query. But I don't know about PHP.

SELECT *
FROM tablename
WHERE id = (SELECT COUNT(*) FROM tablename);

Selfsown

I've seen situations where the last ID values are not related to number of rows in database, for example there have been some rows deleted before. so I don't think using count(*) is a good approach – Marucci