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

TFS 0.X Guild System (guild task,bank) - two SQL problems

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
On Tibia and on OTs there is no good guilds systems like in other games that u can do tasks on guild...

But i found a system that bring it possible to OTs: Advanced Guild System (https://www.xtibia.com/forum/topic/227593-advanced-guild-system/) (with Guild Rank; Guild Tasks; Guild Banker.)

But i got two errors:

1- Add guild balance give a error

When i try to deposit with NPC guild banker, it shows this error:
Code:
[12:58:40.908] mysql_real_query(): UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 50000 WHERE `id` = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)

Same thing when finish guild task system (it should add money to guild bank too):
Code:
[13:51:22.851] mysql_real_query(): UPDATE guilds SET points = (1 + (SELECT points FROM guilds WHERE id = 2)) WHERE id = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)
[13:51:22.851] mysql_real_query(): UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 150000 WHERE `id` = 2 - MYSQL ERROR: Table 'guilds' is specified twice, both as a target for 'UPDATE' and as a separate source for data (1093)

The error looks be in this function:
Code:
function doGuildAddBalance(guild_id, value)
    local func = db.executeQuery or db.query
    func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
    return true
end


Guild bank full code:
Boris.xml
Code:
<npc name="Boris The Guild Banker" script="data/npc/scripts/guildbank.lua" skull="green" floorchange="0" access="5" level="1" maglevel="1">
    <health now="150" max="150"/>
    <look type="132" head="115" body="95" legs="125" feet="115" addons="1" corpse="2212"/>
    <parameters>
        <parameter key="message_greet" value="Welcome to the {guild bank}, |PLAYERNAME|. How can I help you?"/>
        <parameter key="message_farewell" value="Time is money. Remember that." />
        <parameter key="message_walkaway" value="Next please!" />
    </parameters>
</npc>

guildbank.lua
Code:
local keywordHandler = KeywordHandler:new()
local npcHandler = NpcHandler:new(keywordHandler)
NpcSystem.parseParameters(npcHandler)
local talkState = {}

function onCreatureAppear(cid)                npcHandler:onCreatureAppear(cid)            end
function onCreatureDisappear(cid)            npcHandler:onCreatureDisappear(cid)            end
function onCreatureSay(cid, type, msg)            npcHandler:onCreatureSay(cid, type, msg)        end
function onThink()                    npcHandler:onThink()                    end

function writeBankFile(guildname, name, amount, operation)
    local file = io.open("guild_bank/" .. guildname .. ".txt", "a+")
    if file then
        file:write(name .. " - " .. operation .. " - " .. amount .. " - " .. os.date("%c") .. "\n")
        file:close()
        return true
    end
    return false
end

function testGuildFile(guildname)
    local file = io.open("guild_bank/" .. guildname .. ".txt", "r")
    if file then
        file:close()
        return true
    end
    return false
end

function getBankLog(guildname)
    local file = io.open("guild_bank/" .. guildname .. ".txt", "r")
    local content = file:read("*a")
    file:close()
    return content
end

function creatureSayCallback(cid, typ, msg)
    if(not npcHandler:isFocused(cid)) then
        return false
    end

    local talkUser = NPCHANDLER_CONVBEHAVIOR == CONVERSATION_DEFAULT and 0 or cid

    local STG_ACCOUNT = 950
    local STG_WITHDRAW_PERMISSION = 951
    local STG_DEPOSIT_PERMISSION = 952
 
    local guild_rank = getPlayerGuildLevel(cid)  
    local guild = getPlayerGuildId(cid)
    if msgcontains(msg, "guild bank") then
        if not hasGuild(cid) then
            selfSay("This is the Guild Bank, " .. getCreatureName(cid) .. ". You need a guild to open an account here.", cid)
        elseif getGuildStorageValue(guild, STG_ACCOUNT) == 1 then
            selfSay("I see here that your guild already has an account. Would you like to {manage} it?", cid)
            talkState[talkUser] = 1
        elseif guild_rank >= 3 then
            selfSay("Your guild doesn't have an account yet. Would you like to {create} one?", cid)
            talkState[talkUser] = 2
        else
            selfSay("I'm sorry, your guild doesn't have an account with us yet. Please, tell your leader to come and create one here.", cid)
        end
    elseif msgcontains(msg, "manage") and talkState[talkUser] == 1 then
        local str = guild_rank < 3 and "Ok, then. You can check the {balance}, {withdraw} or {deposit}." or "Ok, then. You can check the {balance}, the transaction {list}, {withdraw}, {deposit} or manage account's {permissions}."
        selfSay(str, cid)
        talkState[talkUser] = 3
    elseif msgcontains(msg, "create") and talkState[talkUser] == 2 then
        selfSay("In order to create your account, we need to set some permissions. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders. To withdraw money from the guild account, which permission do you want to set?", cid)
        talkState[talkUser] = 4
    elseif talkState[talkUser] == 4 then
        local permission, permissions = tonumber(msg), {1, 2, 3}
        if isInArray(permissions, permission) then
            setGuildStorageValue(guild, STG_WITHDRAW_PERMISSION, permission)
            selfSay("Ok. Withdraw permission is set. Now, to deposit money into your guild's account, which permission do you want to set?", cid)
            talkState[talkUser] = 5
        else
            selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid)
        end
    elseif talkState[talkUser] == 5 then
        local permission, permissions = tonumber(msg), {1, 2, 3}
        if isInArray(permissions, permission) then
            setGuildStorageValue(guild, STG_DEPOSIT_PERMISSION, permission)
            setGuildStorageValue(guild, STG_ACCOUNT, 1)
            selfSay("Ok. Deposit permission is set and your guild account has been successfully created. Would you like to make a {deposit}?", cid)
            talkState[talkUser] = 3
        else
            selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid)
        end
    elseif msgcontains(msg, "withdraw") and talkState[talkUser] == 3 then
        if guild_rank >= getGuildStorageValue(guild, STG_WITHDRAW_PERMISSION) then
            selfSay("How much would you like to withdraw?", cid)
            talkState[talkUser] = 6
        else
            selfSay("Sorry, but you are not allowed to withdraw money from this account. Speak to your guild leader.", cid)
        end
    elseif talkState[talkUser] == 6 then
        local withdraw_amount = tonumber(msg)
        local guild_balance = getGuildBalance(guild)
        if withdraw_amount and withdraw_amount >= 1 and guild_balance > withdraw_amount then
            withdraw_amount = math.floor(withdraw_amount)
            if doGuildAddBalance(guild, - withdraw_amount) then
                doPlayerAddMoney(cid, withdraw_amount)
                selfSay("Done. Would you like anything else?", cid)
                writeBankFile(getGuildName(guild), getCreatureName(cid), withdraw_amount, "withdraw")
                talkState[talkUser] = 3
            else
                selfSay("An error has ocurred and the transaction couldn't be completed.", cid)
                talkState[talkUser] = 3
            end
        else
            selfSay("You cannot withdraw that amount from your guild's account. Your balance is " .. guild_balance .. ".", cid)
        end
    elseif msgcontains(msg, "deposit") and talkState[talkUser] == 3 then
        if guild_rank >= getGuildStorageValue(guild, STG_WITHDRAW_PERMISSION) then
            selfSay("How much would you like to deposit?", cid)
            talkState[talkUser] = 10
        else
            selfSay("Sorry, but you are not allowed to deposit money into this account. Speak to your guild leader.", cid)
        end
    elseif talkState[talkUser] == 10 then
        local deposit_amount = tonumber(msg)
        local guild_balance = getGuildBalance(guild)
        if deposit_amount and math.floor(deposit_amount) > 0 then
            deposit_amount = math.floor(deposit_amount)
            if doPlayerRemoveMoney(cid,deposit_amount) then
                doGuildAddBalance(guild, deposit_amount)
                writeBankFile(getGuildName(guild), getCreatureName(cid), deposit_amount, "deposit")
                selfSay("Done. Would you like anything else?", cid)
                talkState[talkUser] = 3
            else
                selfSay("I'm sorry, but you do not have that much gold.", cid)
            end
        else
            selfSay("Please, don't waste my time.", cid)
        end
    elseif msgcontains(msg, "balance") and talkState[talkUser] == 3 then
        selfSay("Account's balance is " .. getGuildBalance(guild) .. ". Is there anything else I can help you with?", cid)
     
    elseif msgcontains(msg, "permissions") and talkState[talkUser] == 3 and guild_rank >= 3 then
        selfSay("There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders. To withdraw money from the guild account, which permission do you want to set?", cid)
        talkState[talkUser] = 7
    elseif talkState[talkUser] == 7 then
        local permission, permissions = tonumber(msg), {1, 2, 3}
        if isInArray(permissions, permission) then
            setGuildStorageValue(guild, STG_WITHDRAW_PERMISSION, permission)
            selfSay("Ok. Withdraw permission is set. Now, to deposit money into your guild's account, which permission do you want to set?", cid)
            talkState[talkUser] = 8
        else
            selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid)
        end
    elseif talkState[talkUser] == 8 then
        local permission, permissions = tonumber(msg), {1, 2, 3}
        if isInArray(permissions, permission) then
            setGuildStorageValue(guild, STG_DEPOSIT_PERMISSION, permission)
            selfSay("Ok. Deposit permission is set. Can I help you with anything else?", cid)
            talkState[talkUser] = 3
        else
            selfSay("That wasn't a valid permission. There are 3 permission levels: 1 - members, 2 - vice-leaders, 3 - leaders.", cid)
        end
    elseif msgcontains(msg, "list") and talkState[talkUser] == 3 and guild_rank >= 3 then
        local guildName = getGuildName(guild)
        if not testGuildFile(guildName) then
            selfSay("That option is currently disabled.", cid)
        else
            local str = getBankLog(guildName)
            str = string.explode(str, "\n")
            local newStr = ""
            local tableSize = type(str) == "table" and #str or false
            if not tableSize or (tableSize and tableSize <= 1) then
                selfSay("No transactions have been registered yet. Can I help you with anything else?", cid)
            else
                for i = 0, 9 do
                    local str_add = str[tableSize - 10 + i]
                    if str_add then
                        newStr = newStr .. str_add .. "\n"
                    end
                end
                selfSay("Here you go, the last 10 transactions made in that account.", cid)
                doPlayerPopupFYI(cid, newStr)
            end
        end
    end
    return true
end

npcHandler:setCallback(CALLBACK_MESSAGE_DEFAULT, creatureSayCallback)
npcHandler:addModule(FocusModule:new())





2 Problem two was on installation, one command return an SQL error

This command was necessary (idk what it do):
Code:
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

Shows this SQL error:
Code:
SQL query: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]




CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 )





MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF rank_id ON players
       BEGIN
           UPDATE players
             ' at line 2


All others commands works fine, full commands:
FULL SQL installation commands:
CREATE TABLE guild_storages (
guild_id int NOT NULL default 0,
key int NOT NULL default 0,
value varchar(255) NOT NULL default 0
);

CREATE TRIGGER guilds_update
AFTER UPDATE OF rank_id ON players
BEGIN
UPDATE players
SET guild_id = IFNULL( (
SELECT guild_id
FROM guild_ranks
WHERE id = rank_id
)
, 0 );
END;

ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL
ALTER TABLE guilds ADD balance BIGINT default 0 NOT NULL
ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL
UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE id = rank_id), 0)





Guild System full code lib:
lib/guildlib.lua
Code:
--[[ Queries:
        CREATE TABLE `guild_storages` (
        `guild_id` int NOT NULL default 0,
        `key` int NOT NULL default 0,
        `value` varchar(255) NOT NULL default 0
        );
    
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;
    
        ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL
        ALTER TABLE guilds ADD balance BIGINT default 0 NOT NULL
        ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL
        UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE id = rank_id), 0)
]]

-- Advanced Guild System maintenance functions

function installAdvancedGuildSystem()
    local func = db.query or db.executeQuery
    local query = db.getResult("SELECT balance FROM guilds")
    if query:getID() == -1 then
        func("ALTER TABLE guilds ADD balance INT default 0 NOT NULL")
    else
        query:free()
    end
    local query2 = db.getResult("SELECT points FROM guilds;")
    if query:getID() == -1 then
        func("ALTER TABLE guilds ADD points INT DEFAULT 0 NOT NULL")
    else
        query2:free()
    end
    if func("ALTER TABLE players ADD guild_id INT DEFAULT 0 NOT NULL") then
        if func("UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id), 0)") then
            if func("CREATE TABLE `guild_storages` (`guild_id` INT NOT NULL DEFAULT 0, `key` INT NOT NULL DEFAULT 0, `value` VARCHAR(255) NOT NULL DEFAULT 0);") then
                if func("CREATE TRIGGER [`rank_id update`] AFTER UPDATE OF rank_id ON players BEGIN UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id), 0); END;") then
                    setGlobalStorageValue(50050, 1)
                    print("Advanced Guild System installation successful.")
                    return true
                end
            end
        end
    end
    print("Advanced Guild System could NOT be installed.")
    return false
end

function updateGuildColumn()
    print("Updating guild column in players table...")
    local func = db.query or db.executeQuery
    local str = "failure."
    if func("UPDATE players SET guild_id = IFNULL((SELECT guild_id FROM guild_ranks WHERE `id` = rank_id),0)") then
        str = "success!"
    end
    print("Update was a "..str)
end

isAGSInstalled = getGlobalStorageValue(50050) == 1

-- Guild Points functions

function getGuildPoints(guild_id)
    local ret = db.getResult("SELECT points FROM guilds WHERE id = " .. guild_id)
    if ret:getID() == -1 then
        return -1
    end
    return ret:getDataInt("points")
end

function doGuildAddPoints(guild_id, value)
    local func = db.executeQuery or db.query
    return func("UPDATE guilds SET points = ("..value.." + (SELECT points FROM guilds WHERE id = "..guild_id..")) WHERE id = "..guild_id)
end

-- General guild functions

function hasGuild(cid)
    return getPlayerGuildId(cid) > 0
end

function isInGuild(cid, guildId)
    return getPlayerGuildId(cid) == guildId
end

function getGuildName(guild_id)
    local query = db.getResult("SELECT name FROM guilds WHERE id = "..guild_id)
    local ret = query:getID() == -1 and false or query:getDataString("name")
    query:free()
    return ret
end

function getGuildMembersGUID(guild_id)
    if db.getResult("SELECT ownerid FROM guilds WHERE id = "..guild_id):getID() > -1 then
        local members = {}
        local query = db.getResult("SELECT `id` FROM players WHERE guild_id = "..guild_id)
        if query:getID() == -1 then
            return false
        end
        repeat
            local member = query:getDataInt("id")
            table.insert(members, member)
        until not query:next()
        query:free()
        return members
    end
    return false
end

function getPlayerGuildIdByGUID(guid)
    local query = db.getResult("SELECT guild_id FROM players WHERE id = "..guid)
    return query:getDataInt("guild_id")
end

function getOnlineGuildMembersByRank(guildId, rank, selectLowerRanks)
    if not rank then
        rank = 3
        selectLowerRanks = true
    end
    local list = {}
    for _, pid in pairs(getPlayersOnline()) do
        if getPlayerGuildId(pid) == guildId then
            local playerRank = getPlayerGuildLevel(pid)
            if playerRank == rank or (selectLowerRanks and playerRank <= rank) then
                table.insert(list, pid)
            end
        end
    end
    return list
end

function doFunctionOnGuildMembers(guildId, func)
    local players = getPlayersOnline()
    local count = 0
    for _, pid in pairs(players) do
        if getPlayerGuildId(pid) == guildId then
            func(pid)
            count = count + 1
        end
    end
    return count
end

-- Guild Storage functions

function setGuildStorageValue(guild_id, key, value)
    local func = db.executeQuery or db.query
    local query = db.getResult("SELECT `value` FROM guild_storages WHERE `key` = "..key.." AND guild_id = "..guild_id)
    if query:getID() == -1 then
        return func("INSERT INTO guild_storages (guild_id, `key`, `value`) VALUES ("..guild_id..", "..key..", "..value..")")
    end
    return func("UPDATE guild_storages SET `value` = "..value.." WHERE `key` = "..key.." AND guild_id = "..guild_id)
end

function getGuildStorageValue(guild_id, key)
    local ret = db.getResult("SELECT `value` FROM guild_storages WHERE guild_id = "..guild_id.." AND `key` = "..key)
    if ret:getID() == -1 then
        return -1
    end
    return ret:getDataInt("value") or ret:getDataString("value")
end

-- Guild Balance functions

function getGuildBalance(guild_id)
    local balance = db.getResult("SELECT balance FROM guilds WHERE `id` = "..guild_id)
    local ret = balance:getID() >= 0 and balance:getDataInt("balance") or false
    balance:free()
    return ret
end

function setGuildBalance(guild_id, value)
    local func = db.executeQuery or db.query
    func("UPDATE guilds SET balance = "..value.." WHERE `id` = "..guild_id)
    return true
end

function doGuildAddBalance(guild_id, value)
    local func = db.executeQuery or db.query
    func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
    return true
end

talkactions.xml
Code:
<talkaction words="/agsinstall" access="5" event="script" value="advanced_guild_system_install.lua"/>

advanced_guild_system_install.lua
Code:
function onSay(cid, words, param)
    installAdvancedGuildSystem()
end

Is anybody knows how to fix the problem 1 or 2, or both?

There anything wrong on this SQL func??

Code:
function doGuildAddBalance(guild_id, value)
    local func = db.executeQuery or db.query
    func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
    return true
end

There is any other way to:

1- change this query func:
Code:
function doGuildAddBalance(guild_id, value)
    local func = db.executeQuery or db.query
    func("UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = "..guild_id..") + "..value.." WHERE `id` = "..guild_id)
    return true
end

2- do this query command:
Code:
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

is anybody knows someones who knows SQL here?
 
Solution
This is the way to check?

mysql --version
Code:
mysql  Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.
Installed MariaDB 10.1.48. Imported schema.mysql from TFS 0.4 rev. 3777. Added column 'guild_id' to table 'players'.
Ran this (by DBeaver - Download DBeaver Lite – DBeaver (https://dbeaver.com/download/lite/) , not by phpmyadmin):
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
BEFORE UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        SET new.guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
Everything works. Created new guild, added player...
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
You have to run that query manually, its a trigger that will automatically run if rank_id is changed on the players table.

Sorry, i didn't understand...
"its a trigger that will automatically run if rank_id is changed on the players table"
could explain it more?

---

It would fix the two problems?
Or you are talking about problem 2?
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
About these 2 queries:
Code:
UPDATE guilds SET points = (1 + (SELECT points FROM guilds WHERE id = 2)) WHERE id = 2
UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 150000 WHERE `id` = 2
They are invalid and they can be simplier (and valid):
Code:
UPDATE guilds SET points = points + 1 WHERE id = 2
UPDATE guilds SET balance = balance + 150000 WHERE `id` = 2
We don't have to load value of column from 'same row' in second query (guild id 2 and guild id 2 in second query). Column values are already there and you can use them in math.
 
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
About these 2 queries:
Code:
UPDATE guilds SET points = (1 + (SELECT points FROM guilds WHERE id = 2)) WHERE id = 2
UPDATE guilds SET balance = (SELECT balance FROM guilds WHERE `id` = 2) + 150000 WHERE `id` = 2
They are invalid and they can be simplier (and valid):
Code:
UPDATE guilds SET points = points + 1 WHERE id = 2
UPDATE guilds SET balance = balance + 150000 WHERE `id` = 2
We don't have to load value of column from 'same row' in second query (guild id 2 and guild id 2 in second query). Column values are already there and you can use them in math.

TYYYY <3

you fix the problem one...
i did without the select and everything works o/


---

about the problem two

Code:
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

run this on phpmyadmin isn't run manually?

what it gonna do?

it will update the column 'guild_id' in table 'players'?
when?
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
run this on phpmyadmin isn't run manually?

what it gonna do?

it will update the column 'guild_id' in table 'players'?
when?
Code:
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;
Also looks like invalid SQL code. I wrote version that should work, but I cannot test it now:
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
AFTER UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        UPDATE players SET guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
Execute it in phpmyadmin.
 
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
Code:
        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;
Also looks like invalid SQL code. I wrote version that should work, but I cannot test it now:
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
AFTER UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        UPDATE players SET guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
Execute it in phpmyadmin.

hmmmm then it is a checker for when players join a guild add guild_id on players table...

the query runs fine, but when someone try to join a guild:

Code:
string(48) "UPDATE `players` SET `rank_id`='6' WHERE `id`=14"
(query - SQL error) 
Type: voidQuery (voidQuery is used for update, insert or delete from database)

Can't update table 'players' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

where can i manage this triggers on phpmyadmin?
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
where can i manage this triggers on phpmyadmin?
Go to table players (right side menu) and there should be link to triggers in top menu.

I don't know what is wrong with these triggers. They shouldn't be there. Someone made very bad code, that stopped working after some time - when MySQL stopped accepting invalid SQL code.
 
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
Go to table players (right side menu) and there should be link to triggers in top menu.

I don't know what is wrong with these triggers. They shouldn't be there. Someone made very bad code, that stopped working after some time - when MySQL stopped accepting invalid SQL code.

Wow i can see the trigger there ;)

What are u mean "They shouldn't be there"?
U mean triggers are not the way? Or u mean this is bad?

How the column guild_id in players should change?
There is only it left :(
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
"They shouldn't be there"- guild_id value can be loaded from guilds table, so it should not be stored in players table.

Anyway. This is fixed trigger:
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
BEFORE UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        SET new.guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
 
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
"They shouldn't be there"- guild_id value can be loaded from guilds table, so it should not be stored in players table.

Anyway. This is fixed trigger:
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
BEFORE UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        SET new.guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;

I got this error:
Code:
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '$$ CREATE TRIGGER guilds_update BEFORE UPDATE ON players FOR EACH ROW BEGI' at line 1


(there are some guys with the same problem on brs forum, can i post the solve there with your credits?)

---


Code:
"They shouldn't be there"- guild_id value can be loaded from guilds table, so it should not be stored in players table.

Hmm i think i gotcha, u mean the guys shouldn't copy this info on players when he made that script...
He could consult directlly on guild table, right?
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
No, there is the trigger stuff left
Table players is not updating the column guild_id with the players guild
What mysql version are you using? I prepared that trigger on MySQL 8.0. You are probably using MariaDB 10.x, but I need to know, if it's 10.1, 10.2, 10.3 or 10.4.
 
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
What mysql version are you using? I prepared that trigger on MySQL 8.0. You are probably using MariaDB 10.x, but I need to know, if it's 10.1, 10.2, 10.3 or 10.4.

This is the way to check?

mysql --version
Code:
mysql  Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.
 

Gesior.pl

Mega Noob&LOL 2012
Senator
Premium User
Joined
Sep 18, 2007
Messages
2,387
Solutions
45
Reaction score
1,860
Location
Poland
GitHub
gesior
This is the way to check?

mysql --version
Code:
mysql  Ver 15.1 Distrib 10.1.48-MariaDB, for debian-linux-gnu (x86_64) using readline 5.
Installed MariaDB 10.1.48. Imported schema.mysql from TFS 0.4 rev. 3777. Added column 'guild_id' to table 'players'.
Ran this (by DBeaver - Download DBeaver Lite – DBeaver (https://dbeaver.com/download/lite/) , not by phpmyadmin):
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
BEFORE UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        SET new.guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
Everything works. Created new guild, added player to it and player has guild_id=1 in 'players'.

I don't know what can be a problem. Maybe it's related to phpmyadmin or other tool you are using to send queries to MySQL.
 
Solution
OP
OP
gmstrikker

gmstrikker

Active Member
Joined
Jul 30, 2014
Messages
458
Solutions
1
Reaction score
49
Installed MariaDB 10.1.48. Imported schema.mysql from TFS 0.4 rev. 3777. Added column 'guild_id' to table 'players'.
Ran this (by DBeaver - Download DBeaver Lite – DBeaver (https://dbeaver.com/download/lite/) , not by phpmyadmin):
Code:
DELIMITER $$

CREATE TRIGGER guilds_update
BEFORE UPDATE
ON players FOR EACH ROW
BEGIN
    IF old.rank_id <> new.rank_id THEN
        SET new.guild_id = COALESCE((SELECT guild_id FROM guild_ranks WHERE id = new.rank_id), 0);
    END IF;
END$$

DELIMITER ;
Everything works. Created new guild, added player to it and player has guild_id=1 in 'players'.

I don't know what can be a problem. Maybe it's related to phpmyadmin or other tool you are using to send queries to MySQL.

WOOOOORKS!!!!
thank you!

there are people with the same problem in the other forum...
i gonna post there with your credits!
ty
 
Top