In SQL how to store many data to one field?

[kajahun]

Hey! For example, I have an user table. I have an achievement list store in the scripts.  How can I store the completed achievements for the users in the SQL?

Maybe a third connection table? Because it is a many-to-many connection

Or in the user table somehow?

Edited November 30, 2023 by kajahun
Changed sql to script

[roaddog]

Idk if this what you want but

store it in a table and then use

https://wiki.multitheftauto.com/wiki/ToJSON

 

[IIYAMA]

5 hours ago, kajahun said:

I have an achievement list store in the scripts.  How can I store the completed achievements for the users in the SQL?

As roaddog mentioned, toJSON is the easiest way to dynamic store data. But keep an eye on the limitations of JSON. Only store JavaScript structured default {} objects or [] arrays, nothing in between! (else it will be converted to the default {} object variant)

 

5 hours ago, kajahun said:

Maybe a third connection table? Because it is a many-to-many connection

This would be the most space saving solution. Depending on the amount of players you receive every day, it might be a better solution when there are a lot. Just keep in mind that it will be more work.

 

 

[FlorinSzasz]

4 hours ago, kajahun said:

Hey! For example, I have an user table. I have an achievement list store in the scripts.  How can I store the completed achievements for the users in the SQL?

Maybe a third connection table? Because it is a many-to-many connection

Or in the user table somehow?

I made an example maybe you can get an idea from it how it will work. If you go for toJson/fromJson if you dont have many achievements.

local dbConn = dbConnect("sqlite","users.db")
local users = {}
achievements = {"NewOne","Top1%","TheKiller","OldOne","Nobody","NooB"}

function CreateTableAchievements()
    dbExec(dbConn,"CREATE TABLE IF NOT EXISTS achievements (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,user TEXT,achievements TEXT)")
end 
addEventHandler("onResourceStart",root,CreateTableAchievements)

function onLogin(_,currentAcc)
    if currentAcc then 
        local acc_name = getAccountName(currentAcc)
        if acc_name then 
            local query = dbQuery(dbConn,"SELECT * FROM achievements WHERE user=? LIMIT 1",acc_name)
            local rez = dbPoll(query,-1)
            local data = fromJSON(rez[1]["achievements"])
                if #rez == 0 then 
                dbExec(dbConn,"INSERT INTO achievements (user) VALUES (?)",acc_name)

                elseif #data > 0 then 
                users[source] = data
            end 
        end 
    end 
end 
addEventHandler("onPlayerLogin", root,onLogin)
--- a coomand to give me some achievements
addCommandHandler("newacv",function(player,CommandName,acv)
    if player then 
        if acv ~= nil then 
            if users[player] == nil then 
            users[player] = {} 
            table.insert(users[player],tonumber(acv),achievements[tonumber(acv)])
            end 
                if users[player][tonumber(acv)] == achievements[tonumber(acv)] then 
                outputChatBox("[Achievements] You have this achievement!",player,100,250,100)
                else 
                table.insert(users[player],tonumber(acv),achievements[tonumber(acv)])
                outputChatBox("[Achievements] New achievement unlocked!",player,100,250,100)
            end 
        end 
    end 
end,false,false)

addCommandHandler("saveacv",function(player,CommandName)
    if player then 
            if users[player] ~= nil then 
            local acc_name = getAccountName(getPlayerAccount(player))
            dbExec(dbConn,"UPDATE achievements SET achievements=? WHERE user=?",toJSON(users[player]),acc_name)
        end 
    end 
end,false,false)

 

[kajahun]

Thank you!