如何使用Zend_Db获取Sqlite数据库的最后一个插入ID

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:

我正在尝试在我的PHP应用程序中获取Sqlite DB的最后一个插入的行ID。我正在使用Zend Framework的PDO Sqlite适配器进行数据库处理。 lastInsertId()方法应该给我结果,但它不会。在php.net的PDO文档中,我读到lastInsertId()在所有数据库上的工作方式可能不同。但它根本不适用于sqlite吗?我尝试通过这个覆盖适配器的lastInsertId()方法:

// 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?

但它也不起作用。我每次打电话都会返回0。是否有任何特殊的干净方式来找到最后插入的Id?

5 个解决方案

#1

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

给定带有表b的SQLite3数据库,如下所示:

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

This code gives me a lastInsertId:

这段代码给了我一个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()".

也就是说 - 如果您的表被正确定义,您唯一的问题可能是您尝试获取键$ result [0] - 同样,每当您使用计算列时,我建议使用“AS”对列进行别名关键字,如上所述。如果您不想对列进行别名,则在SQLite3中该列应命名为“last_insert_rowid()”。

#2

PDO::lastInserId()

see: http://us2.php.net/manual/en/pdo.lastinsertid.php

#3

Do not use

不使用

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

instead use

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

SELECT id FROM tablename ORDER DESC LIMIT 1;

#4

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

#5

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

嘿,试试这个查询。但我不知道PHP。

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

#1