Lua:SQLite Tutorial: Part 3

== Part 3: Saving and Retrieving Data ==
This is the final/easiest part of the tutorial. Congratulations, your MySQL is almost ready.

Let's begin:
<lua>
function sql_value_stats ( ply )

end

function sql_value_skills ( ply )

end

function saveStat ( ply )

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

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:
<lua>
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
</lua>

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:
<lua>
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
</lua>
Now for our update:
<lua>
UPDATE player_info SET money = "..money.." WHERE unique_id = '"..unique_id.."'
</lua>
This shouldn't be too hard. It's like select, but we're actually changing things with this.

Likewise with skills:
<lua>
UPDATE player_skills SET speech = ".speech..", fish = "..fish..", farm = "..farm.." WHERE unique_id = '"..unique_id.."'
</lua>

Next, we put it all together, and our saving/loading system is complete:
<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.."'")
	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
</lua>

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


If you had any trouble, go back to [[LUA:MySQL Tutorial|Part 1]] or [[LUA:MySQL Tutorial: Part 2|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.

[[User:Kahn|Kahn]] 21:19, 9 August 2009 (UTC)Kahn