• Help With SQL
    19 replies, posted
I've been working on a gamemode and I wanna save player data. Someone has been helping me with it but we both have hit many issues. I have come here as we can't figure it out. My plans are to save all info of a player. When you spawn in for the first time you create a player. First name, Last name, and model. You'll build your character up and keep him/her alive and your character will persist even if you leave the server (Include saving the current weapons, playermodel, job, money, hunger, health, and armor values). I've been told the only way to do this was through sql. Not all values are being saved as of now. The end goal is to save all the information and be able to call it clientside as well as serverside. This is because I'd like to be able to display the names on a scoreboard and the other values on a hud and setting the player's model and job upon joining again if they left the server without dying. If they die, i'd like to be able to call that persons character as well just in case they were killed in a non-rp way. I'd also like to force them to create a new one after they die. So maybe a manual way of deleting a character from the database would be good too. include("libraries/mysqlite.lua") include("mysql.lua") MySQLite.initialize(RP_MySQLConfig) function CreateTable() MySQLite.tableExists(RP_MySQLConfig.Database_name, function(res)         if not res then          MySQLite.query("CREATE TABLE player_data ( uid bigint(20) NOT NULL, rpname varchar(45) DEFAULT NULL, money int(11) NOT NULL, PRIMARY KEY(uid));")         end     end,     function(e)         print(e)     end ) print("Ran sql") end function GM.NameUsed(name, callback) MySQLite.query("SELECT COUNT(*) AS count FROM player_data WHERE rpname = " .. MySQLite.SQLStr(name) .. ";", function(r)         callback(tonumber(r[1].count) > 0)     end) end function GM.SetPlayerName(ply) if not ply:IsValid() or name == nil then return end GM.NameUsed(name, function(taken) if taken then print("Name Taken") return end MySQLite.query("UPDATE player_data SET rpname = name WHERE uid = " .. ply:SteamID64()) end ) end function GM.GetPlayerData() if not ply:IsValid() then return end MySQLite.query("SELECT * FROM player_data WHERE uid = " .. ply:SteamID64(), function(res) local uid = res[1] local rpname = res[2] local money = res[3] print(uid, rpname, money) end, function(e) print(e) end ) end
So what are you asking for? What is the problem you face?
I guess that's one thing I should've stated more clearly. Nothing I do seems to be working. It's not saving to a database and I can't call anything from it.
Maybe have a look at these pages: SQL In my opinion they can be a good help. But not sure if they are up to date and still working.
I've tried reading that and many searches on google has been done. I've also tried looking at the way darkrp does it and can't seem to figure it out. My last idea was to asks here for help.
What exactly have you done that didn't work? Do you want to use the local SQLite or a real MySQL Database? You also have the option to store data using files if you want.
Well sqlite since I don't have access to a web server. I was told I needed one for a MySQL database. If using files would be easier and not do much to the server, I guess I can try that but also sqlite seems the way to go with the least amount of impact on the server
Follow this tutorial then: LUA:SQLite Tutorial Should cover everything important and should still work. Take care for SQL Injection as mentioned there.
My issue with doing that now (and i'm trying to be as specific as possible) is that i'm getting no errors but when I open up the .db file with db browser. I don't see any information in it so alls I can guess is i'm not saving it correctly? function sql_value_stats( ply ) unique_id = sql.QueryValue("SELECT unique_id FROM player_info WHERE unique_id = '"..steamID.."'") name = sql.QueryValue("SELECT name FROM player_info WHERE unique_id = '"..steamID.."") money = sql.QueryValue("SELECT money FROM player_info WHERE unique_id = '"..steamID.."'") ply:SetNWString("unique_id", unique_id) ply:SetNWString("name", name) ply:SetNWInt("money", money) end function saveStat( ply ) money = ply:GetNWInt("money") name = ply:GetNWString("name") unique_id = ply:GetNWString ("SteamID") sql.Query("UPDATE player_info SET money = "..money.." WHERE unique_id = '"..unique_id.."'") sql.Query("UPDATE player_info SET name = "..name.." WHERE unique_id = '"..unique_id.."'") ply:ChatPrint("Stats updated!") end   function tables_exist()   if (sql.TableExists("player_info")) then Msg("Tables already exist!") else if (!sql.TableExists("player_info")) then query = "CREATE TABLE player_info ( unique_id varchar(255), name varchar(45), money int )" result = sql.Query(query) if (sql.TableExists("player_info")) then Msg("Succes! player table created \n") else Msg("Somthing went wrong with the player_info query! \n") Msg( sql.LastError( result ) .. "\n" ) end end end   end   function new_player( SteamID, ply )   steamID = SteamID sql.Query( "INSERT INTO player_info (`unique_id`, `name`, `money`) VALUES ('"..steamID.."', '100')" ) result = sql.Query( "SELECT unique_id, name, money FROM player_info WHERE unique_id = '"..steamID.."'" ) end   function player_exists( ply )   steamID = ply:GetNWString("SteamID")   result = sql.Query("SELECT unique_id, name, money FROM player_info WHERE unique_id = '"..steamID.."'") if (result) then sql_value_stats( ply ) // We will call this to retrieve the stats else new_player( steamID, ply ) // Create a new player :D end end   function Initialize() tables_exist() end   function PlayerInitialSpawn( ply )   timer.Create("Steam_id_delay", 1, 1, function() SteamID = ply:SteamID() ply:SetNWString("SteamID", SteamID) player_exists( ply )  end )   end   hook.Add( "PlayerInitialSpawn", "PlayerInitialSpawn", PlayerInitialSpawn ) hook.Add( "Initialize", "Initialize", Initialize ) hook.Add("PlayerDisconnected", "SavePlayerDataOnLeave", function( ply ) saveStat( ply ) end )
function PlayerInitialSpawn( ply ) timer.Create("Steam_id_delay", 1, 1, function() SteamID = ply:SteamID() ply:SetNWString("SteamID", SteamID) player_exists( ply ) end ) end Why?
That's what it told me to do.
Please use CREATE TABLE IF NOT EXISTS instead of having two queries, it's painful. Same with update and insert, just make one query that does both things
I see that you are using mysql. It might be easier for you to use Sqlite3 as it's really good for saving data in tables. I don't know where you got the information on mysql being the only way but SQLite3 is the better option and is easier to use(no passwords e.t.c)
They're using the built in Category sql library?
I'd argue that using SQLite3 is easier at first, but if you're looking for something that's powerful and more versatile MySQL is the best option of the two
I don't have a database to use MySQL
Then use builtin SQL db by standard library sql
function CreateTable() sql.Query( "CREATE TABLE player_data( SteamID TEXT, RPName TEXT, Money INTEGER )" ) print("Player database created.") end function NewPlayerToDataBase( ply, RPName, Money ) sql.Query( "INSERT INTO player_data( SteamID, RPName, Money ) VALUES( '"..ply:SteamID().."', '"..RPName.."', 0 )" ) print("Player was added to the database.") end function SavePlayerToDatabase( ply, RPName, Money ) sql.Query( "UPDATE player_data SET RPName="..RPName.." WHERE SteamID='"..ply:SteamID().."'" ) sql.Query( ("UPDATE player_data SET RPName="..RPName.." WHERE RPName=%s"):format(sql.SQLStr(RPName)) ) sql.Query( "UPDATE player_data SET Money="..Money.." WHERE SteamID='"..ply:SteamID().."'" ) print("Player was saved to the database.") end function GM:Initialize() CreateTable() end So this is where i'm at now. It's saving to the database but not updating the name at all. I haven't tested the money though.
Don't create the table every time the server loads. SQL has IF NOT EXISTS Did you call NewPlayerToDataBase?
I have these function GM:PlayerInitialSpawn( ply ) NewPlayerToDataBase( ply, "Joining", 666 ) end net.Receive( "CreateCharacter", function( len, ply )local PlyName = net.ReadString() SavePlayerToDatabase( ply, PlyName, 1000 ) end )
Sorry, you need to Log In to post a reply to this thread.