[HELP] Getting the a value from the last INSET INTO SQLite

[..:D&G:..]

Hello guys, is there any way to get the value of the last data inserted using executeSQLQuery("INSERT INTO") like an ID? I want to make a slot machine system that saves in SQLite and I want each slot machine to have an unique id, and I need to get the ID of the latest slot machine created and then +1.

Anybody any ideas?

[Gallardo9944]

SELECT * FROM yourtable ORDER BY id DESC LIMIT 1 

Should work if you have id column (i think MySQL automatically defines one, SQLite should do that too, not sure).

If you don't have an id, you can add a column with AUTO_INCREMENT type.

[..:D&G:..]

How can I use AUTO_INCREMENT in SQLite?

[Gallardo9944]

CREATE TABLE yourtable (id INT PRIMARY KEY AUTO_INCREMENT NOT NULL , <your other columns>) 

Or change the table you have

ALTER TABLE yourtable MODIFY COLUMN id INT AUTO_INCREMENT 

(2nd example changes a column to be auto increment, but doesn't add it)

[..:D&G:..]

I get syntax error near "dimension"

local id = executeSQLQuery("INSERT INTO `slotmachines`(`id`,`x`,`y`,`z`,`rotation`,`interior',`dimension`) VALUES(?,?,?,?,?,?,?)", id, x, y, z, rotation, interior, dimension ) 
         

[Gallardo9944]

If a value is auto increment, you don't have to insert it. It will set itself automatically.

[..:D&G:..]

Same error Here is the whole function...

function createSlotmachine(thePlayer, ...) 
    if thePlayer and isObjectInACLGroup("user." .. getAccountName(getPlayerAccount(thePlayer)), aclGetGroup("Admin")) then 
        local dimension = getElementDimension(thePlayer) 
        local interior = getElementInterior(thePlayer) 
        local x, y, z  = getElementPosition(thePlayer) 
        local rotation = getPedRotation(thePlayer) 
        z = z - 0.3 
        local id = executeSQLQuery("INSERT INTO `slotmachines`(`x`,`y`,`z`,`rotation`,`interior',`dimension`) VALUES(?,?,?,?,?,?)", x, y, z, rotation, interior, dimension ) 
                     
        if (id) then 
            local object = loadSlotMachines(x, y, z+0.58, 0, 0, rotation-180, interior, dimension) 
            setElementData(object, "dbid", id) 
                     
            local px = x + math.sin(math.rad(-rotation)) * 0.8 
            local py = y + math.cos(math.rad(-rotation)) * 0.8 
            local pz = z 
  
            x = x + ((math.cos(math.rad(rotation)))*5) 
            y = y + ((math.sin(math.rad(rotation)))*5) 
            setElementPosition(thePlayer, x, y, z) 
             
            outputChatBox("Slot machine created with ID #" .. id .. "!", thePlayer, 0, 255, 0) 
        else 
            outputChatBox("There was an error while creating a slot machine Try again.", thePlayer, 255, 0, 0) 
        end 
    end 
end 
addCommandHandler("addslotm", createSlotmachine) 

EDIT - Even though there is an error in the syntax, the slot machine is still spawning but is not added into the db. When I spawn it it doesn't say that I did,..

[Gallardo9944]

The syntax seems to be fine. Try using ?? instead of ?, it will not auto-escape the strings. I used to get errors with ?, but ?? went on fine. Use ' ' when necessary if you're using ??

[..:D&G:..]

Same error, here is a picture of the error:

And the new code:

function createSlotmachine(thePlayer, ...) 
    if thePlayer and isObjectInACLGroup("user." .. getAccountName(getPlayerAccount(thePlayer)), aclGetGroup("Admin")) then 
        local dimension = getElementDimension(thePlayer) 
        local interior = getElementInterior(thePlayer) 
        local x, y, z  = getElementPosition(thePlayer) 
        local rotation = getPedRotation(thePlayer) 
        z = z - 0.3 
        local id = executeSQLQuery("INSERT INTO `slotmachines`(`x`,`y`,`z`,`rotation`,`interior',`dimension`) VALUES(??,??,??,??,??,??)", x, y, z, rotation, interior, dimension ) 
        local dbid = executeSQLQuery("SELECT * FROM slotmachines ORDER BY id DESC LIMIT 1") 
        if (id) then 
            local object = loadSlotMachines(x, y, z+0.58, 0, 0, rotation-180, interior, dimension) 
            setElementData(object, "dbid", dbid) 
                     
            local px = x + math.sin(math.rad(-rotation)) * 0.8 
            local py = y + math.cos(math.rad(-rotation)) * 0.8 
            local pz = z 
  
            x = x + ((math.cos(math.rad(rotation)))*5) 
            y = y + ((math.sin(math.rad(rotation)))*5) 
            setElementPosition(thePlayer, x, y, z) 
             
            outputChatBox("Slot machine created with ID #" .. id .. "!", thePlayer, 0, 255, 0) 
        else 
            outputChatBox("There was an error while creating a slot machine Try again.", thePlayer, 255, 0, 0) 
        end 
    end 
end 
addCommandHandler("addslotm", createSlotmachine) 

[Castillo]

executeSQLQuery ( "INSERT INTO `slotmachines` ( x, y, z, rotation, interior, dimension ) VALUES ( ?, ?, ?, ?, ?, ? )", x, y, z, rotation, interior, dimension ) 

[..:D&G:..]

executeSQLQuery ( "INSERT INTO `slotmachines` ( x, y, z, rotation, interior, dimension ) VALUES ( ?, ?, ?, ?, ?, ? )", x, y, z, rotation, interior, dimension ) 

Finally Thanks! I would also like to ask why I don't get the "Slot Machine created with ID" message. Is it maybe because for the element data "dbid" I get it to be boolean? How can I fix it if so?

[Castillo]

If the query was successfull, I guess you should get the message, try checking what "id" is returning.

[..:D&G:..]

Using outputChatBox it doesn't return anything, any other ways I can check?

It also looks like the machines spawn when I restart the script but they do not appear.

[Castillo]

That's weird.

Try something like:

outputChatBox ( type ( id ) ) 

[Gallardo9944]

As wiki says:

Returns a table with the result of the query if it was a SELECT query, or false if otherwise.

Your query is always false. So your "if id then" will never actually pass.

[Castillo]

I forgot to mention, make sure you put the outputChatBox after defining "id", not after your "if" statement.

[..:D&G:..]

That's weird.

Try something like:

outputChatBox ( type ( id ) ) 

Still doesn't show me any message in the chat box

As wiki says:

Returns a table with the result of the query if it was a SELECT query, or false if otherwise.

Your query is always false. So your "if id then" will never actually pass.

What should I do then instead of "if id then"? I am used to MySQL, and that's what I do in MySQL, never tried saving data in SQLite

[Castillo]

It's pretty much the same thing as with MySQL.

Have you read my last post?

[..:D&G:..]

Have you read my last post?

Oh sorry, haven't seen it. It returns "table"

[Castillo]

It should work then.

[..:D&G:..]

But shouldn't I just get a number? An "ID"?

[Castillo]

No, as the wiki says, it returns a table.

[..:D&G:..]

And how can I make an unique id for every new slot machine spawned?

Or how can I do something like this? :

  
local result = mysqlscr:query("SELECT id, x, y, z, rotation, interior, dimension FROM cows" ) 
local row = mysqlscr:fetch_assoc(result) 
            if not row then break end 
            animals = {} 
            --local s = #animals+1 
             
            local id = tonumber( row["id"] ) 

[..:D&G:..]

Anyone?

Anyway to have

mysql_free_result 

but in SQLite?