LUA:SQLite Tutorial: Part 2

From GMod Wiki

Contents 1 Part 2: Creating a Player Saving/Loading System 2 Creating data in MySQL 3 Check Your Work! 4 End of Part 2

Part 2: Creating a Player Saving/Loading System

We have to start out making sure that our tables exist, so that we can put data into them.

 
function Initialize()
	tables_exist()
end
 

This is going to call our tables_exist() function we made in part 1, on server start up.

Here is where you need to call on that basic Lua knowledge:

 
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
 

It's commented well enough that you should be able to figure it out if you know Lua well enough. If you have no idea what just happened, you need to scour around the Wiki more and come back.

Next up, we're going to be making two functions: player_exists and new_player and then we fetch the networked(NW) variable SteamID.

 
function player_exists ( ply )
	steamID = ply:GetNWString("SteamID")
end
 
function new_player( SteamID, ply )
 
end
 

Next, we're going to be using some SQL code to select a player and load his/her stats. However, if the player isn't found, we need to make a data slot for them:

 
SELECT unique_id, money FROM player_info
 

This is the basic Select statement. It tells the SQL to select the data for manipulation, in this case, the SQL is being told we're going to manipulate the money of unique_id, from player_info.

Now, let's get this into a query format so we can make Lua talk to SQl.

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

The query above, once again, tells the SQL to manipulate the specified data. The added Where statement, tells it where to manipulate the data. It's kind of the like an if then condition.

Now, let's get this all into our function:

 
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
 

The above code, uses our SQL code from before, and puts it into query format. The SQL will then send the results back to Lua. If the result is true, meaning it was able to find the data we were looking for, then it sets up the players stats and skills. However, if it returns false, meaning the data was not found, it will create a new player. Two birds, one stone.

Wait, we don't have a new_player function... That's the next part.

Creating data in MySQL

If you've made it this far, your ready for data creation.

It's recommended, however, that you read the following article on the insert statement, as you'll be using it frequently:

http://www.w3schools.com/sql/sql_insert.asp

Now then, let us create our player function:

 
function new_player( SteamID, ply )
		steamID = SteamID
		sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
		result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" )
end
 

What we just did, was setup a query to change the player_info of unique_id's money, to 100. Then ran a query checking the previous.

Next. we need some error handling for this:

 
function new_player( SteamID, ply )
 
		steamID = SteamID
		sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
		result = sql.Query( "SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'" )
		if (result) then
 
		else
			Msg("Something went wrong with creating a players info !")
		end
end
 

This will tell us if we did something wrong.

Next, we just paste the above, work and make some small modifications:

 
function new_player( SteamID, ply )
 
		steamID = SteamID
		sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
		result = sql.Query( "SELECT unique_id, money 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
				Msg("Player account created !\n")
				sql_value_stats( ply )
				sql_value_skills( ply )
			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
 

There, now our players should get setup just fine!

Check Your Work!

If you've followed the tutorial this far, you should have:

 
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
 
function new_player( SteamID, ply )
 
		steamID = SteamID
		sql.Query( "INSERT INTO player_info (`unique_id`, `money`)VALUES ('"..steamID.."', '100')" )
		result = sql.Query( "SELECT unique_id, money 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
				Msg("Player account created !\n")
				sql_value_stats( ply )
				sql_value_skills( ply )
			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 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
 
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 )
 

You should understand this as well. If you don't, do not continue.

End of Part 2

Well this marks the end of the second part. Make sure you understand everything this far, if you don't, go back to Part 1 or read up on the wiki more. You should be getting pretty good at SQl. If your ready, move on to the third and final part.