Lua:SQLite Tutorial: 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.
<lua>
function Initialize()
	tables_exist()
end
</lua>
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:
<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>
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.
<lua>
function player_exists ( ply )
	steamID = ply:GetNWString("SteamID")
end

function new_player( SteamID, ply )

end
</lua>

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:
<lua>
SELECT unique_id, money FROM player_info
</lua>
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.
<lua>
result = sql.Query("SELECT unique_id, money FROM player_info WHERE unique_id = '"..steamID.."'")
</lua>
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:
<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>
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 SQLite ==

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:
<lua>
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
</lua>
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:
<lua>
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
</lua>
This will tell us if we did something wrong.

Next, we just paste the above, work and make some small modifications:
<lua>
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
</lua>
There, now our players should get setup just fine!

==Check Your Work!==

If you've followed the tutorial this far, you should have:
<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

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 )
</lua>
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 [[Lua:SQLite Tutorial|Part 1]] or read up on the wiki more. You should be getting pretty good at SQl. If your ready, move on to the [[LUA:SQLite Tutorial: Part 3|third and final part]].


{{CatNavFoot|left = Lua:SQLite Tutorial|Part 1|leftcaption = Part 1|middle = Lua_Tutorial_Series|middlecaption = Back to Lua Tutorial Series|right = LUA:SQLite Tutorial: Part 3|rightcaption = Part 3}}