[HELP] sqlite

[AshFTW]

I have connection to db with this content:

+--+----+---+ 
|id|name|hp | 
+--+----+---+ 
|1 |mom |82 | 
|2 |guy |74 | 
|5 |gay |69 | 
|6 |lol |100| 
|7 |sas |93 | 
+--+----+---+ 

So, I wanna to get last available id, it will be 3, then 4, then 8. So, how to write this code?

Also I'm thying this code, but get error

    local q = dbQuery(connect, "SELECT MAX(`id`) FROM `tabname`") 
    res = dbPoll (q, -1) 
    dbFree(q) 
    for k, i in ipairs (res) do 
        outputChatBox("next id is "..res[k]['id']+1) 
[...] 

[Bonsai]

U shouldn't reuse ID's.

[AshFTW]

Okay, so, how to get last id? Pleeeaaase

Edit: I also try SELECT LAST_INSERT_ID(), but again got error

[joaosilva099]

If you set AUTO_INCREMENT on the id field on that table, just pass NULL to the id field when executing an INSERT query and it will auto increment the id. If you want to get the ID which the db incremented to your query use this:

  
local _, _, last_insert_id = dbPoll(dbQuery(CONNECTION_HANDLER, QUERY), -1) 
  

Another way to get the next ID is:

  
local table = dbPoll(...) 
local next_id = table[#table]["id"]+1 
  

[AshFTW]

Oh, thanx Silva

Should I use dbFree()?

So this query to create the table is correct?

    dbExec(connect, "CREATE TABLE IF NOT EXISTS `accounts` (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `nickname` TEXT, `password` TEXT, `email` TEXT);") 

or i shoud use just "`id` INTEGER AUTOINCREMENT"?

[Cadell]

hope this will help you

  
local insertQuery = dbQuery(handler,"SELECT last_insert_rowid() FROM `tablename`") 
                        local iq = dbPoll(insertQuery,-1) 
                        local insertID = iq[1]["last_insert_rowid()"] 
  
  

[joaosilva099]

Oh, thanx Silva

Should I use dbFree()?

So this query to create the table is correct?

    dbExec(connect, "CREATE TABLE IF NOT EXISTS `accounts` (`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, `nickname` TEXT, `password` TEXT, `email` TEXT);") 

or i shoud use just "`id` INTEGER AUTOINCREMENT"?

well i dont usually create tables using sql language i let the hard work to phpmyadmin but i am sure u will find on google

dbFree is not needed when the query is done by dbExec or handled with dbPoll