Lua:SQLite Tutorial: Part 3

From GMod Wiki

Contents 1 Part 3: Saving and Retrieving Data 2 Saving 3 Final Code 4 Credits

Part 3: Saving and Retrieving Data

This is the final/easiest part of the tutorial. Congratulations, your MySQL is almost ready.

Let's begin:

 
function sql_value_stats ( ply )
 
end
 
function sql_value_skills ( ply )
 
end
 
function saveStat ( ply )
 
end
 

We're going to be making three functions here. Let's start with the first one.

We're going to be using a new SQL function here, the sql.QueryValue() function, it works just like the query function, only this function allows us to get a value from the select statement.

 
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.."'")
	ply:SetNWString("unique_id", unique_id)
	ply:SetNWInt("money", money)
end
 

Next, we setup the rest of our variables and the skills function. It's very similar to the one above, in fact just copy paste, and modify:

 
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
 

Now then, onto the most important feature of MySQL, the data saving.

Saving

This is where things pick up. We'll be using a function called "UPDATE". You can "update" the variables in a table with this function. First lets retrieve all the variables we have:

 
function saveStat ( ply )
	money = ply:GetNWInt("money")
	unique_id = ply:GetNWString ("SteamID")
	speech = ply:GetNWInt("speech")
	fish = ply:GetNWInt("fish")
	farm = ply:GetNWInt("farm")
end
 

Now for our update:

 
UPDATE player_info SET money = "..money.." WHERE unique_id = '"..unique_id.."'
 

This shouldn't be too hard. It's like select, but we're actually changing things with this.

Likewise with skills:

 
UPDATE player_skills SET speech = ".speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'
 

Next, we put it all together, and our saving/loading system is complete:

 
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.."'")
	ply:SetNWString("unique_id", unique_id)
	ply:SetNWInt("money", money)
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 saveStat ( ply )
	money = ply:GetNWInt("money")
	unique_id = ply:GetNWString ("SteamID")
	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.." WHERE unique_id = '"..unique_id.."'")
	ply:ChatPrint("Stats updated !") // Added a nice little message
end
 

This marks the end of part 3 and the tutorial.

Final Code

If you followed through the whole thing, your code should look like this:

 
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.."'")
	ply:SetNWString("unique_id", unique_id)
	ply:SetNWInt("money", money)
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 saveStat ( ply )
	money = ply:GetNWInt("money")
	unique_id = ply:GetNWString ("SteamID")
	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.." WHERE unique_id = '"..unique_id.."'")
	ply:ChatPrint("Stats updated !")
end
 
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 )
 

If you had any trouble, go back to Part 1 or Part 2.

---

Credits

Hopefully everyone was able to follow along. This is the end of the tutorial. Thanks to quincy18 for the original tutorial. Kahn for editing/fixing some spelling and grammatical errors, and making it a bit easier to understand.

Kahn 21:19, 9 August 2009 (UTC)Kahn