• 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!

How to select X value from database?

vexler222

Member
Joined
Apr 22, 2012
Messages
504
Solutions
7
Reaction score
15
Hi, i try get value from database cuz i have problem with loading globalevents before lib nvm, but i don't know how, im trying but everytime i got many errors

Code:
function onThink(creature, interval)
local xpp = db.storeQuery("SELECT 'exp' FROM 'boost_creature'")
local lott = db.storeQuery("SELECT 'loot' FROM 'boost_creature'")
local xpp2 = result.getDataString(xpp, 'exp')
local lott2 = result.getDataString(lott, 'loot')

local effects = {
    {position = Position(1007, 994, 7), text = 'Exp +'.. xpp2 ..'%', effect = CONST_ME_BATS, textcolor = 140},
    {position = Position(1009, 994, 7), text = 'Loot +'.. lott2 ..'%', effect = CONST_ME_BATS, textcolor = 140},
    {position = Position(1008, 993, 7), text = 'BOOSTED', effect = CONST_ME_BATS, textcolor = 140},
}
    for i = 1, #effects do
        local settings = effects[i]
        local spectators = Game.getSpectators(settings.position, false, true, 7, 7, 5, 5)
        if #spectators > 0 then
            if settings.text then
               Game.sendAnimatedText(settings.text, settings.position, settings.textcolor)
            end
            if settings.effect then
                settings.position:sendMagicEffect(settings.effect)
            end
             if settings.effect2 then
                settings.position:sendMagicEffect(settings.effect2)
            end
        end
    end
   return true
end
 
Solution
I try tommorow, my table is
name | exp | loot

And i have in table only one value like
Demon | 39 | 32
And i want take value from exp and loot

Then it should work
In case you have several entries and u just want to select the last entry

Change the query to:
Lua:
db.storeQuery("SELECT `exp` FROM `boost_creature` ORDER BY `id` DESC LIMIT 1")
Assuming you have the ID row

Also you should store the value in a globalstorage instead of calling a query everytime

Deatthraz

Advanced OT User
Joined
May 1, 2013
Messages
464
Solutions
30
Reaction score
215
Location
Mexico
Lua:
    local exp = 0
    local resultId = db.storeQuery("SELECT `exp` FROM `boost_creature`")
    if resultId ~= false then
        exp = result.getNumber(resultId, "exp")
        result.free(resultId)
    end

Thats the correct way, however since i dont know your table structure, it probably wont work
So, just add/declare something on 'WHERE' clause if is required or post your table structure
 
Last edited:
OP
OP
vexler222

vexler222

Member
Joined
Apr 22, 2012
Messages
504
Solutions
7
Reaction score
15
Lua:
    local exp
    local resultId = db.storeQuery("SELECT `exp` FROM `boost_creature`"")
    if resultId ~= false then
        exp = result.getNumber(resultId, "exp")
        result.free(resultId)
    end

Thats the correct way, however since i dont know your table structure, it probably wont work
So, just add/declare something on 'WHERE' clause if is required or post your table structure

I try tommorow, my table is
name | exp | loot

And i have in table only one value like
Demon | 39 | 32
And i want take value from exp and loot
 

Deatthraz

Advanced OT User
Joined
May 1, 2013
Messages
464
Solutions
30
Reaction score
215
Location
Mexico
I try tommorow, my table is
name | exp | loot

And i have in table only one value like
Demon | 39 | 32
And i want take value from exp and loot

Then it should work
In case you have several entries and u just want to select the last entry

Change the query to:
Lua:
db.storeQuery("SELECT `exp` FROM `boost_creature` ORDER BY `id` DESC LIMIT 1")
Assuming you have the ID row

Also you should store the value in a globalstorage instead of calling a query everytime
 
Solution
OP
OP
vexler222

vexler222

Member
Joined
Apr 22, 2012
Messages
504
Solutions
7
Reaction score
15
Then it should work
In case you have several entries and u just want to select the last entry

Change the query to:
Lua:
db.storeQuery("SELECT `exp` FROM `boost_creature` ORDER BY `id` DESC LIMIT 1")
Assuming you have the ID row

Also you should store the value in a globalstorage instead of calling a query everytime

Work perfecto, thanks
 

Ramon Bernardo

Active Member
Joined
Oct 7, 2018
Messages
88
Solutions
2
Reaction score
46
Do not do this. This will overflow the dispatcher thread with querys. Use at least asyncQuery.

@edit
This will not be updated if the db updates. Make a global var and an event to check the time and thus update.
There are better ways to do this, take it as a draft.
Lua:
local boostExp, boostLoot

function onThink(creature, interval)
    if not boostExp and not boostLoot then
        local resultId = db.storeQuery("SELECT `exp`, `loot` FROM `boost_creature` ORDER BY `id` DESC LIMIT 1")
        if resultId then
            boostExp = result.getNumber(resultId, "exp")
            boostLoot = result.getNumber(resultId, "loot")
            result.free(resultId)
        end
        return true
    end

    local effects = {
        { position = Position(1007, 994, 7), text = "Exp +".. boostExp .."%", effect = CONST_ME_BATS, textcolor = 140 },
        { position = Position(1009, 994, 7), text = "Loot +".. boostLoot .."%", effect = CONST_ME_BATS, textcolor = 140 },
        { position = Position(1008, 993, 7), text = "BOOSTED", effect = CONST_ME_BATS, textcolor = 140 },
    }

    for i = 1, #effects do
        local settings = effects[i]
        local spectators = Game.getSpectators(settings.position, false, true, 7, 7, 5, 5)
        if #spectators > 0 then
            if settings.text then
               Game.sendAnimatedText(settings.text, settings.position, settings.textcolor)
            end

            if settings.effect then
                settings.position:sendMagicEffect(settings.effect)
            end
        end
    end
   return true
end
 
Last edited:
OP
OP
vexler222

vexler222

Member
Joined
Apr 22, 2012
Messages
504
Solutions
7
Reaction score
15
Do not do this. This will overflow the dispatcher thread with querys. Use at least asyncQuery.

@edit
This will not be updated if the db updates. Make a global var and an event to check the time and thus update.
There are better ways to do this, take it as a draft.
Lua:
local boostExp, boostLoot

function onThink(creature, interval)
    if not boostExp and not boostLoot then
        local resultId = db.storeQuery("SELECT `exp`, `loot` FROM `boost_creature` ORDER BY `id` DESC LIMIT 1")
        if resultId then
            boostExp = result.getNumber(resultId, "exp")
            boostLoot = result.getNumber(resultId, "loot")
            result.free(resultId)
        end
        return true
    end

    local effects = {
        { position = Position(1007, 994, 7), text = "Exp +".. boostExp .."%", effect = CONST_ME_BATS, textcolor = 140 },
        { position = Position(1009, 994, 7), text = "Loot +".. boostLoot .."%", effect = CONST_ME_BATS, textcolor = 140 },
        { position = Position(1008, 993, 7), text = "BOOSTED", effect = CONST_ME_BATS, textcolor = 140 },
    }

    for i = 1, #effects do
        local settings = effects[i]
        local spectators = Game.getSpectators(settings.position, false, true, 7, 7, 5, 5)
        if #spectators > 0 then
            if settings.text then
               Game.sendAnimatedText(settings.text, settings.position, settings.textcolor)
            end

            if settings.effect then
                settings.position:sendMagicEffect(settings.effect)
            end
        end
    end
   return true
end
I create two functions in global.lua and its work from first post, but you talking it bad idea? I dont understand, this first script update value every time when global event make effect?
 

Deatthraz

Advanced OT User
Joined
May 1, 2013
Messages
464
Solutions
30
Reaction score
215
Location
Mexico
I create two functions in global.lua and its work from first post, but you talking it bad idea? I dont understand, this first script update value every time when global event make effect?

Yes, the query will be update/executed everytime the script is executed, if you have 1 second interval, then it will update every 1 second thats why is not good idea in this case

Best way to do it is by storing values 'exp' and 'boost' in a global storage as i said before
 
OP
OP
vexler222

vexler222

Member
Joined
Apr 22, 2012
Messages
504
Solutions
7
Reaction score
15
Yes, the query will be update/executed everytime the script is executed, if you have 1 second interval, then it will update every 1 second thats why is not good idea in this case

Best way to do it is by storing values 'exp' and 'boost' in a global storage as i said before

So if i make globalstorage queery be updated only one time after server start or what?
I was tryed but i dont know how to make it
 
Last edited:
Top