• There is NO official Otland's Discord server and NO official Otland's server list. The Otland's Staff does not manage any Discord server or server list. Moderators or administrator of any Discord server or server lists have NO connection to the Otland's Staff. Do not get scammed!

Lua Collect all ids of a column located within a database table

MorganaSacani

Active Member
Joined
Sep 20, 2022
Messages
87
Solutions
1
Reaction score
32
Hello World!
I created an auxiliary table for my database called 'test_server_storage'.
In this table there are two columns. The 'player_id' column and the 'item_id' column.
I'm trying to collect all ids that are in the columns called 'item_id' of a certain player, in this case 'player_id'. And after collecting the ids, I'm trying to make these columns receive the value 0.
I started by creating a 'while do' loop structure, but it only works once.
The function collects only the first value of the 'item_id' column and then clears all columns of the same name.
This is the part of my algorithm I'm struggling to get working.
I don't understand much about SQL commands:
Lua:
local playerGuid = player:getGuid()
local inbox = player:getSlotItem(CONST_SLOT_STORE_INBOX)
while playerGuid == result.getNumber(db.storeQuery("SELECT player_id FROM test_server_storage WHERE player_id = " .. playerGuid), "player_id") do
    inbox:addItem(itemId_, 1) -- Here I need my algorithm to add all the id present in the 'item_id' column for my character.

    db.query("UPDATE test_server_storage SET item_id = 0")
    db.query("UPDATE test_server_storage SET player_id = 0")
end
Does anyone know how to help me?
Post automatically merged:

I remembered one more problem:
In addition to this algorithm cleaning all 'item_id', it cleans all 'player_id', even though they are not the same id as my player.
 
When I use this query inside my database, in the SQL area:
SQL:
SELECT item_id FROM test_server_storage WHERE player_id = 6 AND item_id > 0;
The value of all columns called 'item_id' of my player with id 6 is returned to me. For example:
3387
3388
3389
3420

But, when I try to make the same query inside my server, the algorithm returns only the first value, for example:
3387

I tested printing on the console.
Post automatically merged:

On my server I use the query this way:
Lua:
result.getNumber(db.storeQuery("SELECT item_id FROM test_server_storage WHERE player_id = " .. player:getGuid() .. " AND item_id > 0;"), "item_id")
 
You need to call this at the end of your loop:

Code:
result.next(resultId)

Also I think the loop may be wrong.

I would do this this way (taken from tfs)

Code:
    local resultId = db.storeQuery("SELECT player_id, item_id FROM test_server_storage WHERE player_id = " .. playerGuid)

    if resultId then
        repeat
                local playerId = result.getNumber(resultId, "player_id")
                local balance = result.getNumber(resultId, "balance")

        until not result.next(resultId)
        result.free(resultId)
    end

And your query with "SELECT" missed the item_id attribute, so I've added it.

And to clear by player ID, you can do the following:

Code:
db.query("DELETE FROM test_server_storage WHERE player_id = " .. playerId)

Regards :p
 
Thanks Slaw! You helped me a lot. Now it's working and here's the solution in case anyone has the same question:
Lua:
if resultId then
    repeat
        local playerId = result.getNumber(resultId, "player_id")
        db.query("DELETE FROM test_server_storage WHERE player_id = " .. playerId)
        local itemId = result.getNumber(resultId, "item_id")
        print(itemId)
    until not result.next(resultId)
    result.free(resultId)
end
 
Back
Top