LAST_INSERT_ID()

[Noki]

Hello,

I'm trying to get the last insert ID from a MySQL table (using LAST_INSERT_ID()). As seen below, the table does auto increment and it does have a primary key.

Table creation code (unedited in phpMyAdmin):

dbExec(db, "CREATE TABLE IF NOT EXISTS `housing` (`houseID` INT NOT NULL AUTO_INCREMENT, `owner` TEXT, `interiorID` TINYINT, `x` FLOAT, `y` FLOAT, `z` FLOAT, `houseName` TEXT, `currentPrice` INT, `boughtForPrice` INT, `initialPrice` INT, `sale` TINYINT, `open` TINYINT, PRIMARY KEY(houseID))") 

Code I'm using to retrieve the value (which doesn't work):

local qh = dbQuery(db, "SELECT LAST_INSERT_ID()") 
local result = dbPoll(qh, -1) 

Any help would be greatly appreciated.

[novo]

I haven't got an exact answer for your issue (though either way you haven't set a FROM statement together with LAST_INSERT_ID) but you may use this workaround.

  
SELECT MAX(houseID) FROM housing; 
  

[Noki]

Let's say I select it and that query works. Using it with dbQuery will return a query handler, which you poll with dbPoll. dbPoll returns a table. What do I do to get the MAX(houseID) from that table?

[Mr_Moose]

As a table from a database is built on a various amount of rows but a constant amount of columns you can use a loop function like pairs or ipairs to go through all the rows, it might also work to just grab index 1 by doing something like this:

result["ID"][1] 

If that doesn't work, go for the loop method as seen in below example:

for _, row in pairs(result) do  
    outputChatBox(row["ID"]) 
end 

Assuming "ID" is the name of the column who's max value you want to retrieve and "result" is the result from your database (a table).

[Noki]

Whichever one I do returns nil. I've tried to get the max column value before, and with the same result.

Odd.

[Noki]

Bump.

[Jusonex]

Try the following code:

local qh = dbQuery(db, "SELECT LAST_INSERT_ID() AS `ID`") 
local result = dbPoll(qh, -1) 
local id = result[1].ID 

As a better alternative, you can use dbPoll's third returned value.

Returns a table when the query has successfully completed. This automatically frees the query handle, so you do not have to call dbFree.

This also returns extra values:

* int: number of affected rows

* int: last insert id

https://wiki.multitheftauto.com/wiki/DbPoll

==>

local result, affectedRows, houseId = dbPoll(dbQuery(db, "INSERT INTO housing (owner, interiorId, bla, ...) VALUES(...)"), -1) 

[Noki]

And here I am not even checking the Wiki pages for something I may have missed.

Thanks, Jusonex. I'm not able to test it right now, but I will let you know if it worked!

Edit: it worked, thanks a heap!

[-ffs-AbodyRulez]

Hello,

I'm trying to get the last insert ID from a MySQL table (using LAST_INSERT_ID()). As seen below, the table does auto increment and it does have a primary key.

Table creation code (unedited in phpMyAdmin):

dbExec(db, "CREATE TABLE IF NOT EXISTS `housing` (`houseID` INT NOT NULL AUTO_INCREMENT, `owner` TEXT, `interiorID` TINYINT, `x` FLOAT, `y` FLOAT, `z` FLOAT, `houseName` TEXT, `currentPrice` INT, `boughtForPrice` INT, `initialPrice` INT, `sale` TINYINT, `open` TINYINT, PRIMARY KEY(houseID))") 

Code I'm using to retrieve the value (which doesn't work):

local qh = dbQuery(db, "SELECT LAST_INSERT_ID()") 
local result = dbPoll(qh, -1) 

Any help would be greatly appreciated.

Sorry for bumping this up, but i've been trying to create an Auto_increment column, tried to follow your method, but i keep getting an error dbExec failed, syntax error, any ideas?

[Noki]

Try surrounding the primary key bit at the end with backticks. Also, post your code.