SQLite counting database items

[Miika]

Hi,

I'm currently making SQLite based house system, it's first time when i work with databases.

So how can i get number of database items? I need to get house id and put it to pickup data.

My code:

function newHouse(int, x, y, z, r, name, price) 
    pic = createPickup(x, y, z, 3, 1273, 0) 
    -- id = ??? <---- i need to count, how many items there are inside the table 
    setElementData(pic, "id", id + 1) 
    setElementData(pic, "name", name) 
    setElementData(pic, "status", "sale") 
    setElementData(pic, "price", price) 
    setElementData(pic, "int", int) 
    dbExec(database, "INSERT INTO houselist (houseName, houseOwner, housePrice, houseForSale, housePosition, houseInterior) VALUES (?, ?, ?, ?, ?, ?)", tostring(name), "", tonumber(price), "No", x .. ", " .. y .. ", " .. z .. ", " .. r, int) 
    triggerClientEvent (client, "closeGUI", client) 
end 
addEvent("houses_new:createNewHouse", true) 
addEventHandler("houses_new:createNewHouse", resourceRoot, newHouse) 

My table:

database = dbConnect("sqlite", "house_database.db") 
dbExec(database, "CREATE TABLE IF NOT EXISTS 'houselist'(id INTEGER PRIMARY KEY, houseName TEXT, houseOwner TEXT, housePrice bigint(255), houseForSale TEXT, housePosition TEXT, houseInterior INT)" ) 

I hope you understand something what i mean

[Walid]

Try to use this make sure to add ID column into your db.

function getFreeID() 
    local result = dbPoll(dbQuery(con, "SELECT ID FROM houselist ORDER BY ID ASC"), -1) 
    newID = false 
    for i, id in pairs (result) do 
        if id["ID"] ~= i then 
            newID = i 
            break 
        end 
    end 
    if newID then  
        return newID  
    else  
        return #result + 1  
    end 
end 

[Miika]

Got it work, thank you very much!

[Miika]

New question

How can i get single column name and output it to chatbox in given row?

I mean something like this:

for i=1, getFreeID() do 
    local result = dbExec(database, "SELECT * FROM houselist WHERE id = ?", i, houseList) 
    outputChatBox(result[i]) 
end 

i is the row number and i want to get value from houseList column and then output it to chabox.

[Walid]

try sth like this

local query = dbQuery(database, "SELECT * FROM houselist" ) 
local result, numrows = dbPoll(query, -1) 
if (result and numrows > 0) then 
    for index, row in pairs(result) do 
        local id = row['id'] 
        local houseName = row['houseName'] 
        local houseOwner = row['houseOwner'] 
        -- bla bla bla  
        -- You code here 
    end 
end 

[Miika]

You did it again, thanks!

[Walid]

you are welcome.