sqlite - how do I get a one row result back? (luaSQLite3)

In order to get a single row use the db:first_row method. Like so.

row = db:first_row("SELECT `id` FROM `table`")print(row.id)

In order to get the row count use the SQL COUNT statement. Like so.

row = db:first_row("SELECT COUNT(`id`) AS count FROM `table`")print(row.count)

EDIT: Ah, sorry for that. Here are some methods that should work.

You can also use db:nrows. Like so.

rows = db:nrows("SELECT `id` FROM `table`")row = rows[1]print(row.id)

We can also modify this to get the number of rows.

rows = db:nrows("SELECT COUNT(`id`) AS count FROM `table`")row = rows[1]print(row.count)

Here is a demo of getting the returned count:

> require "lsqlite3"> db = sqlite3.open":memory:"> db:exec "create table foo (x,y,z);"> for x in db:urows "select count(*) from foo" do print(x) end0> db:exec "insert into foo values (10,11,12);"> for x in db:urows "select count(*) from foo" do print(x) end1> 

Just loop over the iterator you get back from the rows or whichever function you use. Except you put a break at the end, so you only iterate once.

Getting the count is all about using SQL. You compute it with the SELECT statement:

SELECT count(*) FROM ...

This will return one row containing a single value: the number of rows in the query.