Sql Tutorial

[Quarternions]

:eng101: [b]Sql Tutorial :[/b]

Im fairly new to lua I've been here for about a month but couldn't find a single sql tutorial and since I did PHP/SQL it wasn't hard for me to write a database system. So I'm writing this for you.

In this tutorial we are making a money and stat system.
Save it in you gamemode folder as sql_database.lua

[highlight] The code may not be as optimized as it could be, since I'm fairly new to Gmod Lua [/highlight]

[b]What do we need ?[/b]

Pre knowledge :

- A basic knowlege of sql (Im gonna write a small tutorial about that in the first section)
- Some lua experience

Actual coding :

- A database setup function
- A players setup function
- A retrieving function
- A saving function

[b]Basic sql knowlegde :[/b]

[highlight]This is a crash course and does not teach advanced topics[/highlight]

Sql has diffrent parts, a database, tables and rows.

A database can be called a main file to store your tables and a table is where you store you're different variables.
Look at this basic schematic to get a better view of what im saying :

[img]http://i383.photobucket.com/albums/oo273/FPtje/waq7h0.jpg[/img]

I suggest you read these pages :

[url]http://www.w3schools.com/sql/sql_syntax.asp[/url]
[url]http://www.w3schools.com/sql/sql_select.asp[/url]
[url]http://www.w3schools.com/sql/sql_where.asp[/url]
[url]http://www.w3schools.com/sql/sql_insert.asp[/url]
[url]http://www.w3schools.com/sql/sql_update.asp[/url]

these explain some of the basics of sql that I cant do in this post, if you just read them through it shouldnt take more 10 minutes. After that you will have some better understanding of what I'm saying.

[b]Actual coding :[/b]

Fortunate, Gmod automaticly creates a database and we only have to create the tables.

So lets get started.

We begin by creating a function called tables_exist and adding a if statement to see if the table we want to make exists
[lua]
function tables_exist()
if sql.TableExists("player_info") && sql.TableExists("player_skills") then //sql.TableExists does exactly what it says
Msg("Both tables already exist !")
else
// Create the tables
end
end
[/lua]
Next we need to check if either one of the tables doesn't exist and if they don't create them
[lua]
function tables_exist()
if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then // ! = not
// Create the table here
end
if (!sql.TableExists("player_skills")) then
// Create the table here
end
end
end
[/lua]

Now lets do some basic sql to create a table :

CREATE TABLE player_info

Basic create command, now add some collums

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

Here I added a variable to hold our query (What we instruct the database to do) and added a unique id for the steam id
so we can sort somebody out of our database (varchar mean that it can be a number or a letter)
we can do the same for the other table :

query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"

Now lets add that to our code :
[lua]
function tables_exist()
if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
result = sql.Query(query)
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)
end
end
end
[/lua]
As you can see I added sql.Query. This function allows us to send commands to the database.
and save any error's that we have encountered if there are any.

Now lets check if the database was created :
[lua]
function tables_exist()

if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
result = sql.Query(query)
if (sql.TableExists("player_info")) then
Msg("Succes ! table 1 created \n")
else
Msg("Somthing 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("Succes ! table 2 created \n")
else
Msg("Somthing went wrong with the player_skills query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
end

end
[/lua]

As you see I only added a basic if statement and some messages to display us if it worked.
Next posts for the other parts

[editline]10:17PM[/editline]

[b]Part 2 : Creating a player creating/loading system.[/b]

So lets get started. Lolz I forgot to tell you how we call these function.

Well this one is pretty simple :
[lua]
function Initialize()
tables_exist()
end
[/lua]
This calls the tables_exists function when the server starts up.

Now somthing a bit harder :
[lua]
function PlayerInitialSpawn( ply )
// Fires after the player spawned for the first time

timer.Create("Steam_id_delay", 1, 1, function()
// Sets up a little delay cause otherwise the steamid wont be available yet

SteamID = ply:SteamID()
// Sets the variable SteamID to the players steamID

ply:SetNWString("SteamID", SteamID)
// Networks the variable so we can use it everywhere

timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
// Creates a timer loop that repeats infinite every 10 secconds to save our stats (Whe get into that later)

player_exists( ply )
// Calls our player exists function

end)
end

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

// Clean code :

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
[/lua]
Well That wasn't to hard wasn't it ?

Sorry about that "Cough" Now lets move on

Create 2 functions : player_exists and new_player and then we fetch the networked(NW) variable SteamID
[lua]
function player_exists ( ply )
steamID = ply:GetNWString("SteamID")
end

function new_player( SteamID, ply )

end
[/lua]
Now some more sql :
We need to select someone from the database and if there is a result we can load his stats and if there isn't a result we need to create the player.

So :

SELECT unique_id, money FROM player_info

Basic select statement, we select the unique id and the money from the player info table.
lets add some stuff :

[lua]result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")[/lua]

DON'T PANIC ! I just added the where part and the sql.query. The where part allows us to check if the unique ID is the same as the one we provided.
[lua]
function player_exists( ply )

steamID = ply:GetNWString("SteamID")

result = sql.Query("SELECT unique_id, money 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
else
new_player( steamID, ply ) // Create a new player :D
end
end
[/lua]
As you see I added 2 functions to retrieve stuff cause I find that easier you can mix them in 1 function if you want.

Lets move on to the create a new player part. We need to insert a new player into the database with the values we want

[url]http://www.w3s

[kevkev]

Wow, thats one hell of a post, thanks for this, i finally understand some of it :)

[Quarternions]

Yea I know But as you may have seen in the last post I tried to split it only automerge :(

Any comments are welcome :D

[wizzy]

[QUOTE=quincy18;16227030]:eng101: [b]Sql Tutorial :[/b]

Im fairly new to lua I've been here for about a month but couldn't find a single sql tutorial and since I did PHP/SQL it wasn't hard for me to write a database system. So I'm writing this for you.

In this tutorial we are making a money and stat system.
Save it in you gamemode folder as sql_database.lua

[highlight] The code may not be as optimized as it could be, since I'm fairly new to Gmod Lua [/highlight]

[b]What do we need ?[/b]

Pre knowledge :

- A basic knowlege of sql (Im gonna write a small tutorial about that in the first section)
- Some lua experience

Actual coding :

- A database setup function
- A players setup function
- A retrieving function
- A saving function

[b]Basic sql knowlegde :[/b]

[highlight]This is a crash course and does not teach advanced topics[/highlight]

Sql has diffrent parts, a database, tables and rows.

A database can be called a main file to store your tables and a table is where you store you're different variables.
Look at this basic schematic to get a better view of what im saying :

[img]http://i25.tinypic.com/waq7h0.jpg[/img]

I suggest you read these pages :

[url]http://www.w3schools.com/sql/sql_syntax.asp[/url]
[url]http://www.w3schools.com/sql/sql_select.asp[/url]
[url]http://www.w3schools.com/sql/sql_where.asp[/url]
[url]http://www.w3schools.com/sql/sql_insert.asp[/url]
[url]http://www.w3schools.com/sql/sql_update.asp[/url]

these explain some of the basics of sql that I cant do in this post, if you just read them through it shouldnt take more 10 minutes. After that you will have some better understanding of what I'm saying.

[b]Actual coding :[/b]

Fortunate, Gmod automaticly creates a database and we only have to create the tables.

So lets get started.

We begin by creating a function called tables_exist and adding a if statement to see if the table we want to make exists
[lua]
function tables_exist()
if sql.TableExists("player_info") && sql.TableExists("player_skills") then //sql.TableExists does exactly what it says
Msg("Both tables already exist !")
else
// Create the tables
end
end
[/lua]
Next we need to check if either one of the tables doesn't exist and if they don't create them
[lua]
function tables_exist()
if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then // ! = not
// Create the table here
end
if (!sql.TableExists("player_skills")) then
// Create the table here
end
end
end
[/lua]

Now lets do some basic sql to create a table :

CREATE TABLE player_info

Basic create command, now add some rows

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

Here I added a variable to hold our query (What we instruct the database to do) and added a unique id for the steam id
so we can sort somebody out of our database (varchar mean that it can be a number or a letter)
we can do the same for the other table :

query = "CREATE TABLE player_skills ( unique_id varchar(255), speech int, fish int, farm int )"

Now lets add that to our code :
[lua]
function tables_exist()
if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
result = sql.Query(query)
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)
end
end
end
[/lua]
As you can see I added sql.Query. This function allows us to send commands to the database.
and save any error's that we have encountered if there are any.

Now lets check if the database was created :
[lua]
function tables_exist()

if (sql.TableExists("player_info") && sql.TableExists("player_skills")) then
Msg("Both tables already exist !")
else
if (!sql.TableExists("player_info")) then
query = "CREATE TABLE player_info ( unique_id varchar(255), money int )"
result = sql.Query(query)
if (sql.TableExists("player_info")) then
Msg("Succes ! table 1 created \n")
else
Msg("Somthing 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("Succes ! table 2 created \n")
else
Msg("Somthing went wrong with the player_skills query ! \n")
Msg( sql.LastError( result ) .. "\n" )
end
end
end

end
[/lua]

As you see I only added a basic if statement and some messages to display us if it worked.
Next posts for the other parts

[editline]10:17PM[/editline]

[b]Part 2 : Creating a player creating/loading system.[/b]

So lets get started. Lolz I forgot to tell you how we call these function.

Well this one is pretty simple :
[lua]
function Initialize()
tables_exist()
end
[/lua]
This calls the tables_exists function when the server starts up.

Now somthing a bit harder :
[lua]
function PlayerInitialSpawn( ply )
// Fires after the player spawned for the first time

timer.Create("Steam_id_delay", 1, 1, function()
// Sets up a little delay cause otherwise the steamid wont be available yet

SteamID = ply:SteamID()
// Sets the variable SteamID to the players steamID

ply:SetNWString("SteamID", SteamID)
// Networks the variable so we can use it everywhere

timer.Create("SaveStat", 10, 0, function() saveStat( ply ) end)
// Creates a timer loop that repeats infinite every 10 secconds to save our stats (Whe get into that later)

player_exists( ply )
// Calls our player exists function

end)
end

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

// Clean code :

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
[/lua]
Well That wasn't to hard wasn't it ?

Sorry about that "Cough" Now lets move on

Create 2 functions : player_exists and new_player and then we fetch the networked(NW) variable SteamID
[lua]
function player_exists ( ply )
steamID = ply:GetNWString("SteamID")
end

function new_player( SteamID, ply )

end
[/lua]
Now some more sql :
We need to select someone from the database and if there is a result we can load his stats and if there isn't a result we need to create the player.

So :

SELECT unique_id, money FROM player_info

Basic select statement, we select the unique id and the money from the player info table.
lets add some stuff :

[lua]result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")[/lua]

DON'T PANIC ! I just added the where part and the sql.query. The where part allows us to check if the unique ID is the same as the one we provided.
[lua]
function player_exists( ply )

steamID = ply:GetNWString("SteamID")

result = sql.Query("SELECT unique_id, money 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
else
new_player( steamID, ply ) // Create a new player :D
end
end
[/lua]
As you see I added 2 functions to retrieve stuff cause I find that easier you can mix them in 1 function if you want.

Lets move on to the create a new player part. We need to insert a new player into the database with the values we want

[url]http://www.w3schools.com/sql/

[Deadchicken]

[QUOTE=wizzy;16230867]way too long for lua beginners, but hey, its a good intro on how to make some more [b]SHITTY DARKRP SCRIPTS WITH MONEY[/b][/QUOTE]

What an awful thing to say, the guy's spent a serious bit of time there, explaining things to all of those that wish to learn, indeed, I will peruse this when I have the time, and am very thankful for it.

How ignorant of you.

[ChewGum]

Pretty nice.

UN-QUOTE THAT FUCKING SHIT WIZZY!

[Waffler]

[QUOTE=wizzy;16230867]way too long for lua beginners, but hey, its a good intro on how to make some more [b]SHITTY DARKRP SCRIPTS WITH MONEY[/b][/QUOTE]

Don't need to quote the whole post...

[kahn]

Wow, great tutorial. Even though I knew how to use MySQL, skimming through this showed me a few little things I never realized. I gave you a useful haha.
Thanks again.

[Pred101]

Lol.......... Wonder if he accually understands LUA at all his self :P

[kahn]

[QUOTE=Pred101;16232053]Lol.......... Wonder if he accually understands LUA at all his self :P[/QUOTE]

I'm sure you don't.

[Pred101]

[QUOTE=kahn;16232064]I'm sure you don't.[/QUOTE]

Nope hence why i am reading it nit wit

[cody8295]

how do i make it so that it saves all the stats and shit to like data/players ....

[Capsup]

[QUOTE=quincy18;16227030]:eng101: [b]Sql Tutorial :[/b]

Tutorial...

[/QUOTE]

Actually, this is really awesome. I sat down one day earlier and tried to learn all this, I kinda succeeded, but I must that you have shown me a little trick or two with this. Thank you.

[QUOTE=Pred101;16232082]Nope hence why i am reading it nit wit[/QUOTE]

Learn lua yourself please, before criticising this as it's very well made. And how the hell will you even learn any lua by reading this? :O

[Quarternions]

[QUOTE=wizzy;16230867]way too long for lua beginners, but hey, its a good intro on how to make some more [b]SHITTY DARKRP SCRIPTS WITH MONEY[/b][/QUOTE]

Did I ever say this was made for lua beginners ? And remove the quote to my post its annoying ....

[conman420]

Very nice. I will have to read this in full soon!

[Quarternions]

Any other tutorial requests ?

[commander204]

I suggest that you put that on the WIki. Why? This thread will surely Die soon. Not bcause its not good but bcause soon everyone saw it and wont have a reason to re-post into it.

[Deco Da Man]

You can merge those two tables into one...

Otherwise.. pretty good.
Put it on the Wiki. On the forums it'll just get forgotten and the thread will time-warp.

[Quarternions]

[QUOTE=Deco Da Man;16239985]You can merge those two tables into one...

Otherwise.. pretty good.
Put it on the Wiki. On the forums it'll just get forgotten and the thread will time-warp.[/QUOTE]

I know, I added it cause it shows people how to do multiple tables. And il put it on wiki later cause it takes some effort to change all the tags etc.

[Deco Da Man]

Also, explaining how to use a relational database in an object-oriented way may be good idea. Specifically by explaining how to use foreign keys to relationships like the kind shown in [url=http://www.facepunch.com/showthread.php?t=764773]this thread[/url].

[kevkev]

Alright question, why does it say that i am using the wrong syntax when i use this query :

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

Exact error message :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wd_donators' WHERE steamid = 'UNKNOWN'' at line 12

[editline]08:24PM[/editline]

Note that this is done with the MySQL module, and not in SQLite.

[Quarternions]

''wd_donators' WHERE steamid = 'UNKNOWN''

Is supposed to be this :

'wd_donators' WHERE steamid = 'UNKNOWN'

It might have to do with the input your giving.

[eemmzz]

Your use of queries is a bit inefficient, if it wasn't a tutorial for SQL Lite I would have died inside seeing you do stuff like this:

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.."'")

Also Steam ID's are server side anyway so why on earth are you using GetNWString for it? That bit baffled me.

Otherwise it is nice to see someone write a tutorial for people who have never done it before so good work, just a few weird ways of doing stuff in there to me personally. I know you mentioned your red text in the tutorial saying you're fairly new to it but I decided to say the above anyway.

[esalaka]

Can't you string.format( "SELECT * FROM player_skills WHERE unique_id = '%s'", steamID ) in SQLite? (Yes, I'm addicted to string.format >.< )

[Quarternions]

[QUOTE=Emz;16245378]Your use of queries is a bit inefficient, if it wasn't a tutorial for SQL Lite I would have died inside seeing you do stuff like this:

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.."'")

Also Steam ID's are server side anyway so why on earth are you using GetNWString for it? That bit baffled me.

Otherwise it is nice to see someone write a tutorial for people who have never done it before so good work, just a few weird ways of doing stuff in there to me personally. I know you mentioned your red text in the tutorial saying you're fairly new to it but I decided to say the above anyway.[/QUOTE]

Yeah normaly I wouldn't do it like this. But for some reason the way I do it in php won't work. Any suggestion for improving ?

And about the GetNWString, I modified my own database script ( A bit larger ) for this tutorial so their could be some unoptimized bits in it

Edit :

I'l release another tutorial on this page soon cause I found out how to optimize it a bit more cause of emz.

[kevkev]

[QUOTE=quincy18;16244559]

''wd_donators' WHERE steamid = 'UNKNOWN''

Is supposed to be this :

'wd_donators' WHERE steamid = 'UNKNOWN'

It might have to do with the input your giving.[/QUOTE]

The extra ' you see is added by the error message, it is not in my code.

[Quarternions]

[b]Sql Tutorial optimization :[/b]

For starters you can remove every GetNWString/SetNWString which you use to get the players steamID since you can call it when you need it by doing this :

[lua]
unique_id = ply:SteamID()
[/lua]

Syntax should be pretty straight forward ... ply is the player object and SteamID() is to well get the steamID.

Next we change some querying cause I didn't know there was a function so you could get a whole row in Gmod.

So This :
[lua]
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:SetNWInt("speech", speech)
ply:SetNWInt("fish", fish)
ply:SetNWInt("farm", farm)
[/lua]

We change to this :

[lua]
function sql_value_skills ( ply )
table1 = sql.QueryRow("SELECT * FROM player_skills WHERE unique_id = '"..steamID.."'")
speech = table1["speech"]
fish = table1["fish"]
farm = table1["farm"]
ply:SetNWInt("speech", speech)
ply:SetNWInt("fish", fish)
ply:SetNWInt("farm", farm)
end
[/lua]
Il explain it here so don't worry.

So the way we did it before was to send a query for each of the values and if you have lots of values thats going to lag and its not very efficient. (Displayed by the colored squares)

[img]http://i29.tinypic.com/w2g3rt.jpg[/img]

What we are doing now is sending a query to pick up the whole row so every value in 1 query and storing them in a table. Then we just put them into loose variables ( You don't need to do that but I do that cause its easier to show) and send them to our Networked variables.

Thats all I got for now, I'l add some more if someone reports some other bugs/un-efficient things.

[Skondra]

[QUOTE=kevkev;16243339]Alright question, why does it say that i am using the wrong syntax when i use this query :

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

Exact error message :

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''wd_donators' WHERE steamid = 'UNKNOWN'' at line 12

[editline]08:24PM[/editline]

Note that this is done with the MySQL module, and not in SQLite.[/QUOTE]

Try this:

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

[eemmzz]

That is much better, good job mate.

As a second note, whenever you concatenate a variable into a query, it is good to use a "clean version" to avoid SQL injections.

In sql lite: local CleanSteamID = sql.SQLStr( SteamID );

In tmysql: local CleanSteamID = tmysql.escape( SteamID );

[Quarternions]

Never that of that cause It is serverside (on my gamemode) so I thought the users had no input.
(Can you change ur steamid clientside ? :O)