How to select X value from database?

[vexler222]

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

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

 

[Roddet]

    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

 

[vexler222]

    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

 

[Roddet]

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:

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

 

[vexler222]

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

Change the query to:

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]

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.

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

 

[vexler222]

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.

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?

 

[Roddet]

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

 

[vexler222]

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