Sql Tutorial

[commander204]

No you cant.

[Quarternions]

[QUOTE=commander204;16267786]No you cant.[/QUOTE]

The good old boxes, well I only asked cause I see many people use the mysql escape feature on the steamID...

[kevkev]

[QUOTE=Skondra;16265162]Try this:

("SELECT * FROM "..Table.." WHERE steamid = "..tostring(sid))

[/QUOTE]

Thanks, but i already fixed it (The same way as you did).

[eemmzz]

Well looking on google you can use Cheat Engine to change your Steam ID.

Therefore you should not assume your server side code is secure. Any value being concatenated should be cleaned.

(Bet the person who rated that dumb has no idea about SQL at all, so congratulations to them. :D )

[Quarternions]

[b]Adding some extra security :[/b]

I don't think you need to get explained about this but il just do it in case of :

Before injecting anything into the database that could be hacked to change the input or if its just a user input use this : sql.SQLStr(variablewithuser/maybehackedinput) you can also build you query first and then run the function over it

Example :

[lua]
query = "SELECT * FROM names WHERE name = " .. SQLStr( ply:Name() )
query = sql.SQLStr( "SELECT count(*) from ratings" )

// I dont know if this works never tried it :

query = SQLStr("SELECT * FROM names WHERE name = " .. ply:Name() )
[/lua]

Il test it out later can't right now.

[hexpunK]

I'm still fairly new to lua, but I have a feeling I might be able to learn this particular bit of it easily, as I enjoy working with databases for some reason, so I understand some of the concepts behind this. I'll read it properly tommorow and maybe have a go at making something to see if I understood it, this tutorial did explain it well.

[Quarternions]

Could anyone put this on gmod wiki, I don't want it to get lost and I tried but I'm not that good with wiki.

[kahn]

I'll work on doing that for you right now :P.
What's your steam friends. You can help me edit it as I do it.

[editline]05:24PM[/editline]

There, ported over to the wiki tutorial series. If you don't like anything I did, feel free to change it.
[url]http://wiki.garrysmod.com/?title=Lua_Tutorial_Series#quincy18.27s_MySQL_Lua_Tutorial[/url]

Thanks again for the tutorial.

[Sparky-Z]

Finally! I've been looking for something like this for a while now, all the gamemode guides on the wiki are usually more guided towards deathmatches and killing kind of things. With this tutorial I may be able to learn how to code other kinds of gamemodes with stats and a money system =D

[DarkSpider]

I need to learn JOINS

[GsE-Lance]

i gotta learn how to add values ( skills ) and gained money to the data file now D: any one with a good link?

[lucasmontec]

Thank you for this tutorial....i just want to know....how do i clear the database?

[Helix Alioth]

You are fucking epic.

[Quarternions]

Please know that this code is a bit messy, it was also 1 of the first things I ever did in lua.

[ArgwmFedon]

Hello, I'm having trouble with the MySQL.
I Added 5 new statistics for it to save. At first, I had all 5 in "player_info" but only XP would save. So I made a new Table for the 4 other stats, and I get this error.

"
Timer Error: ****\gamemode\MySQL.lua:47: attempt to concatenate global 'power' (a boolean value)
"
Here is the function

function saveStat ( ply )
    money = ply:GetNWInt("money")
    unique_id = ply:GetNWString ("SteamID")
    XP = ply:GetNWInt("XP")
    agility = ply:GetNWInt("agility")
    smarts = ply:GetNWInt("smarts")
    stamina = ply:GetNWInt("stamina")
    power = ply:GetNWInt("power")
    speech = ply:GetNWInt("speech")
    fish = ply:GetNWInt("fish")
    farm = ply:GetNWInt("farm")
    sql.Query("UPDATE player_skills SET speech = "..speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'")
    sql.Query("UPDATE player_info SET money = "..money..", XP = "..XP.." WHERE unique_id = '"..unique_id.."'")
    sql.Query("UPDATE player_attributes SET agility = "..agility..", smarts = "..smarts..", stamina = "..stamina..", power = "..power.." WHERE unique_id = '"..unique_id.."'")
    ply:ChatPrint("Stats updated !")
end

And the error says Timer Error, so Here is the Timer.

function PlayerInitialSpawn( ply )
 
    timer.Create("Steam_id_delay", 1, 1, function()
        SteamID = ply:SteamID()
        ply:SetNWString("SteamID", SteamID)
        timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
        player_exists( ply ) 
    end)
 
end

If you need the whole code, Just say so.

Any help here? I've tried everything I know. (Which isn't saying much.)
I hear that a Boolean value, means True/False/Null, but I have those stats the exact same setup, as the others. and the others work perfectly.

[Quarternions]

Post your whole code in lua tags.

[ArgwmFedon]

[lua]
function sql_value_stats ( ply )
unique_id = sql.QueryValue("SELECT unique_id FROM player_info WHERE unique_id = '"..steamID.."'")
money = sql.QueryValue("SELECT money FROM player_info WHERE unique_id = '"..steamID.."'")
XP = sql.QueryValue("SELECT XP FROM player_info WHERE unique_id = '"..steamID.."'")
ply:SetNWString("unique_id", unique_id)
ply:SetNWInt("money", money)
ply:SetNWInt("XP", XP)
end

function sql_value_skills ( ply )
unique_id = sql.QueryValue("SELECT unique_id FROM player_skills WHERE unique_id = '"..steamID.."'")
speech = sql.QueryValue("SELECT speech FROM player_skills WHERE unique_id = '"..steamID.."'")
fish = sql.QueryValue("SELECT fish FROM player_skills WHERE unique_id = '"..steamID.."'")
farm = sql.QueryValue("SELECT farm FROM player_skills WHERE unique_id = '"..steamID.."'")
ply:SetNWString("unique_id", unique_id)
ply:SetNWInt("speech", speech)
ply:SetNWInt("fish", fish)
ply:SetNWInt("farm", farm)
end

function sql_value_attributes ( ply )
unique_id = sql.QueryValue("SELECT unique_id FROM player_attributes WHERE unique_id = '"..steamID.."'")
agility = sql.QueryValue("SELECT agility FROM player_attributes WHERE unique_id = '"..steamID.."'")
smarts = sql.QueryValue("SELECT smarts FROM player_attributes WHERE unique_id = '"..steamID.."'")
stamina = sql.QueryValue("SELECT stamina FROM player_attributes WHERE unique_id = '"..steamID.."'")
power = sql.QueryValue("SELECT power FROM player_attributes WHERE unique_id = '"..steamID.."'")
ply:SetNWString("unique_id", unique_id)
ply:SetNWInt("agility", agility)
ply:SetNWInt("smarts", smarts)
ply:SetNWInt("stamina", stamina)
ply:SetNWInt("power", power)
end

function saveStat ( ply )
money = ply:GetNWInt("money")
unique_id = ply:GetNWString ("SteamID")
XP = ply:GetNWInt("XP")
agility = ply:GetNWInt("agility")
smarts = ply:GetNWInt("smarts")
stamina = ply:GetNWInt("stamina")
power = ply:GetNWInt("power")
speech = ply:GetNWInt("speech")
fish = ply:GetNWInt("fish")
farm = ply:GetNWInt("farm")
sql.Query("UPDATE player_skills SET speech = "..speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'")
sql.Query("UPDATE player_info SET money = "..money..", XP = "..XP.." WHERE unique_id = '"..unique_id.."'")
sql.Query("UPDATE player_attributes SET agility = "..agility..", smarts = "..smarts..", stamina = "..stamina..", power = "..power.." WHERE unique_id = '"..unique_id.."'")
ply:ChatPrint("Stats updated !")
end

function tables_exist()

if (sql.TableExists("player_info") && sql.TableExists("player_skills") && sql.TableExists("player_attributes")) then
Msg("All tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int, XP int )"
result = sql.Query(query)
if (sql.TableExists("player_info")) then
Msg("Success ! table 1 created \n")
else
Msg("Something went wrong with the player_info query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
if (!sql.TableExists("player_skills")) then
query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"
result = sql.Query(query)
if (sql.TableExists("player_skills")) then
Msg("Success ! table 2 created \n")
else
Msg("Something went wrong with the player_skills query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
if (!sql.TableExists("player_attributes")) then
query = "CREATE TABLE player_attributes ( unique_id varchar(255), agility int, smarts int, stamina int, power int )"
result = sql.Query(query)
if (sql.TableExists("player_attributes")) then
Msg("Success ! table 3 created \n")
else
Msg("Something went wrong with the player_attributes 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`, `money`, `XP`)VALUES ('"..steamID.."', '100', '1')" )
result = sql.Query( "SELECT unique_id, money, XP FROM player_info WHERE unique_id = '"..steamID.."'" )
if (result) then

sql.Query( "INSERT INTO player_skills (`unique_id`, `speech`, `fish`, `farm`)VALUES ('"..steamID.."', '1', '1', '1')" )
result = sql.Query( "SELECT unique_id, speech, fish, farm FROM player_skills WHERE unique_id = '"..steamID.."'" )
if (result) then

sql.Query( "INSERT INTO player_attributes (`unique_id`, `agility`, `smarts`, `stamina`, `power`)VALUES ('"..steamID.."', '1', '1', '1', '1')" )
result = sql.Query( "SELECT unique_id, agility, smarts, stamina, power FROM player_attributes WHERE unique_id = '"..steamID.."'" )
if (result) then
Msg("Player account created !\n")
sql_value_stats( ply )
sql_value_skills( ply )
sql_value_attributes( ply )

else
Msg("Something went wrong with creating a players attributes !\n")
end

else
Msg("Something went wrong with creating a players skills !\n")
end

else
Msg("Something went wrong with creating a players info !\n")
end
end

function player_exists( ply )

steamID = ply:GetNWString("SteamID")

result = sql.Query("SELECT unique_id, money, XP FROM player_info WHERE unique_id = '"..steamID.."'")
if (result) then
sql_value_stats( ply ) // We will call this to retrieve the stats
sql_value_skills( ply ) // We will call this to retrieve the skills
sql_value_attributes( ply ) // We will call this to ****ING WORK!
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)
timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
player_exists( ply )
end)

end

hook.Add( "PlayerInitialSpawn", "PlayerInitialSpawn", PlayerInitialSpawn )
hook.Add( "Initialize", "Initialize", Initialize )

[/lua]

[Gbps]

Cool code, now what?

[ArgwmFedon]

Well, I'm guessing the problem is inside the new_player function, because Before I made a new table for the Attributes, That's where my error was... But, The table looks the exact same as the rest of them. I do not understand what is causing it to be true/false, instead of numeric.

Do you have any suggestions?

Edit: I realized what the problem was... I had to delete all the current data in the database. Thanks for everyone who helped me anyways.

[Quarternions]

Sorry for not responding. I had some business to attend to.

[antid2]

Great tutorial, is there a way to print the database to a txt file? or even just print it in console?

[Quarternions]

Haven't tested this but this will give you a nudge in the right direction.
[lua]
table1 = sql.QueryRow("SELECT * FROM player_skills WHERE unique_id = '"..steamID.."'")
for k,v in pairs(table1) do
file.Write("mod/data.txt", v.."\n")
end
[/lua]

[antid2]

im a PHP/SQL programmer myself, And i remember i saw somewhere you could. How would you go about using an actually database? mysql.connect etc.

[B]EDIT:[/B]

nevermind, i found the module

[Quarternions]

snip

[antid2]

[url]http://www.facepunch.com/showthread.php?t=893578[/url] ??

[conman420]

Hmm... looking at your code, you don't create a unique timer for the initial spawn. If two players spawn at the same second it would overwrite the previous timer function and cause their stats to no be loaded.

[FPtje]

[quote=Quincy18]Basic create command, now add some rows

query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"

[/quote]
Quincy18 you are mistaken with the rows and columns
You say colums are rows. They are not.
Here's a random picture from google to explain:
[img]http://2.bp.blogspot.com/_1EZkGmmHBwc/SzH84cWdS4I/AAAAAAAABRg/xUTx2U_WZ3w/s400/SQL+Table.JPG[/img]

You think ID, name and ParentID are rows. They are not. Those are the columns.
1 Product 0 is a row
2 Geography 0 is also a row.

"Geography" is the contents of a cell.

Here's the fixed version of your picture:
[IMG]http://i383.photobucket.com/albums/oo273/FPtje/waq7h0.jpg[/IMG]

[Quarternions]

[QUOTE=FPtje;20187131]Mistake[/QUOTE]

Holy shit how could I make a mistake like that :O

Thank you sir.I'l add your picture to the tut

[antid2]

it's stupid, but i always remember columns because it has a l in the name! verticle..stupid but works for me :)

[r0cky]

[QUOTE=ArgwmFedon;20002976]Well, I'm guessing the problem is inside the new_player function, because Before I made a new table for the Attributes, That's where my error was... But, The table looks the exact same as the rest of them. I do not understand what is causing it to be true/false, instead of numeric.

Do you have any suggestions?

Edit: I realized what the problem was... I had to delete all the current data in the database. Thanks for everyone who helped me anyways.[/QUOTE]

im having this problem how exactly did you fix it?