7 minutos atrás, Rusherzin disse:

I'm not sure if it's going to work because I did not test the code.
Anyway, I think you can do something like this:
 

local TItoDelete = db.storeQuery("SELECT `player_id`,`pid`,`sid`,CONVERT(`attributes` USING latin1 ) AS `attr` FROM `player_items` WHERE CONVERT(`attributes` USING latin1 ) LIKE '%timerOnExp%'")

    if TItoDelete then -- if the MySQL result is not empty
        repeat
            local attrstring = result.getString(TItoDelete, "attr") -- get the attribute
            local attrnumber = tonumber(string.sub(attrstring, -12, string.len(attrstring)-2)) -- here I try to get only the number (I'm not sure if it will work)
            if (os.time() >= attrnumber) then
		local playerid = result.getNumber(TItoDelete, "player_id")
		local pid      = result.getNumber(TItoDelete, "pid")
                local sid      = result.getNumber(TItoDelete, "sid")
            	db.storeQuery("DELETE FROM `player_items` WHERE `player_id`="..playerid.." AND `pid` = "..pid.." AND `sid` ="..sid) -- deletes item
           	end
        until not result.next(TItoDelete)
    end

By the way, this one is just an example that should work for `player_items`.

 

:7: bad argument #1 to 'len' <string expected, got boolean)

Line 7:

            local attrnumber = tonumber(string.sub(attrstring, -12, string.len(attrstring)-2)) -- here I try to get only the number (I'm not sure if it will work)

 

 

Can not this be adapted to do what I'm looking for?

Anti-Dupe - Delete all items with duplicated serial from your database.

tablesToCheck = {"player_items", "player_depotitems", "tile_items", {"player_items", {"player_depotitems", "tile_items"}}, {"player_depotitems", "tile_items"}}
 
function onStartup()
    local text, final = "", ""
    local filex = "data/logs/duplicated.txt"
    local f = io.open(filex, "a+")
    local count = 0
    for i = 1, table.maxn(tablesToCheck) do
        if type(tablesToCheck[i]) == "string" then
            local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 1)")
            if query:getID() ~= -1 then
                while(true) do
                    local delete = db.executeQuery("delete from " .. tablesToCheck[i] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. " and player_id = " .. query:getDataInt("player_id") .. ";")
                    text = "[!] -> Deleting items with duplicated serial from '" .. tablesToCheck[i] .. "': [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                    count = (delete and count + 1 or count)
                    final = final .. (final ~= "" and "\n" or "") .. text
                    print(text)
                    if not query:next() then break end
                end
            end
        else
            if type(tablesToCheck[i][2]) == "string" then
                local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                if query:getID() ~= -1 then
                    while(true) do
                        local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                        local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                        count = (delete and count + 1 or count)
                        local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                        count = (delete2 and count + 1 or count)
                        text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
                        "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                        final = final .. (final ~= "" and "\n" or "") .. text
                        print(text)
                        if not query:next() then break end
                    end
                end
            else
                for j = 1, #tablesToCheck[i][2] do
                    local query = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][1] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][2][j] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                    if query:getID() ~= -1 then
                        while(true) do
                            local query_ = db.getResult("SELECT *, SUBSTRING(CONVERT(attributes USING latin1) FROM 18) AS 'track' FROM " .. tablesToCheck[i][2][j] .. " WHERE SUBSTRING(CONVERT(attributes USING latin1) FROM 18) IN (SELECT SUBSTRING(CONVERT(attributes USING latin1) FROM 18) FROM " .. tablesToCheck[i][1] .. " WHERE CONVERT(attributes USING latin1) LIKE '%serial%' GROUP BY SUBSTRING(CONVERT(attributes USING latin1) FROM 18) HAVING COUNT(*) > 0)")
                            local delete = db.executeQuery("delete from " .. tablesToCheck[i][1] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                            count = (delete and count + 1 or count)
                            local delete2 = db.executeQuery("delete from " .. tablesToCheck[i][2][j] .. " where SUBSTRING(CONVERT(attributes USING latin1) FROM 18) = " .. db.escapeString(query:getDataString("track")) .. ";")
                            count = (delete2 and count + 1 or count)
                            text = "[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][1] .. "' [Player: " .. getPlayerNameByGUID(query:getDataInt("player_id")) .. ", Item: " .. query:getDataInt("itemtype") .. ", Count: " .. query:getDataInt("count") .. ", Serial: " .. query:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!") ..
                            "\n[!] -> Deleting item with duplicated serial from '" .. tablesToCheck[i][2][j] .. "' [Player: " .. getPlayerNameByGUID(query_:getDataInt("player_id")) .. ", Item: " .. query_:getDataInt("itemtype") .. ", Count: " .. query_:getDataInt("count") .. ", Serial: " .. query_:getDataString("track") .."]... " .. (delete and "Success!" or "Failed!")
                            final = final .. (final ~= "" and "\n" or "") .. text
                            print(text)
                            if not query:next() then break end
                        end
                    end
                end
            end
        end
    end
    if f ~= nil then
        f:write("[" .. os.date("%d %B %Y %X ", os.time()) .. "] >> [Anti-Dupe] " .. count .. " duplicated items have been deleted from the database.\n" .. (final == "" and "[!] -> No duplicated item was found in the database" or final) .. "\n\n")
        f:close()
    else
        print("[!] -> [Anti-Dupe] Cannot save info to file!")
    end
    return true
end
 

 

I'm sorry, but I don't know why it's returning a bool instead of a string.