Mysql database

Right, so im creating a salary/payday system for my garrys mod gamemode, basically it connects to a database in GM:Initialize inside init.lua

code:
I replaced the db info with dummy info as i dont wanna give out that information.



function GM:Initialize()
require ("mysqloo")

local DB_HOST = "host"
local DB_PORT = port
local DB_NAME = "name"
local DB_USERNAME = "username"
local DB_PASSWORD = "password"

function connectToDatabase()
	databaseObject = mysqloo.connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_PORT)
	databaseObject.onConnected = function() print("DB Linked!") end
	databaseObject.onConnectionFailed = function() print("Failed to connect to db.") end
	databaseObject:connect()
end

connectToDatabase()

function checkQuery(query)
	local playerInfo = query:getData()
	if playerInfo[1] ~= nil then
		return true
	else
		return false
	end
end

function FirstJoinMysql( ply )
	local query1 = databaseObject:query("SELECT * FROM RLRP_Players WHERE SteamID = '" .. ply:SteamID() .. "'")
	query1.onSuccess = function(q)
		if not checkQuery(q) then
			local query2 = databaseObject:query("INSERT INTO RLRP_Players(SteamID, Money) VALUES ('" .. ply:SteamID() .. "', " .. 15000 .. ")")
			query2.onSuccess = function(q) print("Created " .. ply:Name() .. "'s Character") end
			query2.onError = function(q,e) print("Could not create " .. ply:Name() .. "'s character! (Something went wrong!)") end
			query2:start()
		else
			print( ply:Name() .. " already exists in the database!")
		end
	end
	query1.onError = function(q,e) print("Something went wrong when checking if the user was already in the database.") end
	query1:start()
end
end


That works perfectly, it adds the steamid and the starting money to database. All works from there.

However in my payday system i am needing a way to change the “15000” value (money) to the current value + salary
Like this: v:AddMoney(50)

AddMoney should add the number (50) to the current amount of money and update the database. Then save it of course.

My question is, how do i access the database from another file? How do i create the functions for getting and adding, saving money?

That’s it! I hope someone is wanting to help me as i really want mysql in my gamemode.

Thank you a lot!

dont put it inside the init hook, put it inside the top of the init file/seperate file, and then include the file you create the functions, it should work fine then.

Didn’t quite get that, but did you mean for me to create a file for the database connection example

db_connection.lua
and put the database code in there, and then when i need to use database, include the db_connection.lua and then i should be able to use the functions there?

that SHOULD work, and dont put it inside a hook, its useless.

Here’s what he’s trying to get at



require ("mysqloo")

local DB_HOST = "host"
local DB_PORT = port
local DB_NAME = "name"
local DB_USERNAME = "username"
local DB_PASSWORD = "password"

function connectToDatabase()
	databaseObject = mysqloo.connect(DB_HOST, DB_USERNAME, DB_PASSWORD, DB_NAME, DB_PORT)
	databaseObject.onConnected = function() print("DB Linked!") end
	databaseObject.onConnectionFailed = function() print("Failed to connect to db.") end
	databaseObject:connect()
end

connectToDatabase()

function checkQuery(query)
	local playerInfo = query:getData()
	if playerInfo[1] ~= nil then
		return true
	else
		return false
	end
end

function FirstJoinMysql( ply )
	local query1 = databaseObject:query("SELECT * FROM RLRP_Players WHERE SteamID = '" .. ply:SteamID() .. "'")
	query1.onSuccess = function(q)
		if not checkQuery(q) then
			local query2 = databaseObject:query("INSERT INTO RLRP_Players(SteamID, Money) VALUES ('" .. ply:SteamID() .. "', " .. 15000 .. ")")
			query2.onSuccess = function(q) print("Created " .. ply:Name() .. "'s Character") end
			query2.onError = function(q,e) print("Could not create " .. ply:Name() .. "'s character! (Something went wrong!)") end
			query2:start()
		else
			print( ply:Name() .. " already exists in the database!")
		end
	end
	query1.onError = function(q,e) print("Something went wrong when checking if the user was already in the database.") end
	query1:start()
end

function GM:Initialize()
--other init code
end


Thank you! Ill try to do that when im home, however ill make a database.lua file so i can access the database in other files. Thanks!

For your salary query, you could do:



UPDATE RLRP_Players SET `Money` = `Money` + [amount] WHERE `SteamId` = '[steamid]'


Replace the square bracketed items with the relevant values (remove the brackets also). This method means you don’t have to look up the amount of money someone has, add to it in Lua and then save the value.

Also if you are saving user input (such as giving money), make sure you make your values SQL safe. It’s a shame the mysqloo library doesn’t seem to support prepared statements as that would make life a bit easier.

Thanks, not good at query yet! how do i make the “values” sql safe?

could i do

function AddMoney(amount, steamid)
UPDATE RLRP_Players SET Money = Money + amount WHERE SteamId = ‘steamid’
end

Sort of, obviously with the proper syntax.

Making values SQL safe involves making sure the input data matches what the DB is expecting. For example, if your Money column is an integer (number), we want to prevent any other type being inserted (especially if we’re accepting user input).



require( "mysqloo" )

-- Assume we have a local "db" variable which contains the database connection.

function addMoney( steamid, amount )
	
	if type(amount) == "number" then
	
		local qry = db:query( "UPDATE RLRP_Players SET `Money` = `Money` + " .. amount .. " WHERE `SteamId` = '" .. steamid .. "'" );
		
		qry.onSuccess = function( q )
			-- do stuff here (or not)
		end
		
	else
		-- Amount wasn't a number, throw an error or something.
	end
	
end


It’s been a loooooooong time since I’ve written Lua but that’s the general idea. For string values from user input, we would need to use the escape function. Here’s an example using a theoretical “saveJobTitle” function:



function saveJobTitle( steamid, title )

	local safe_title = db:escape( title )
	
	local qry = db:query( "UPDATE RLRP_Players SET `JobTitle` = '" .. safe_title .. "' WHERE `SteamId` = '" .. steamid .. "'" )
	
	qry.onSuccess = function( q )
	 -- success stuff...
	end

end


The escape() function there makes the input string (title) safe for running as part of the MySQL query.

As an example, this is the query run if someone’s job title is saved as “Prime Minister”:



UPDATE RLRP_Players SET `JobTitle` = 'Prime Minister' WHERE `SteamID` = 'steamid'


We’re accepting user input which means we’re liable to SQL injection attacks.

If someone was to set their job title to this:

The script would run the following query



UPDATE RLRP_Players SET `JobTitle` = 'Prime Minister'; TRUNCATE TABLE RLRP_Players; SELECT ''


Which would set the title as specified, but would also run two extra queries afterwards, one to delete all the rows in the table and another dummy select statement to complete the open quote mark.

By escaping user input, we escape the “dangerous” characters within the input. The query becomes:



UPDATE RLRP_Players SET `JobTitle` = 'Prime Minister\'; TRUNCATE TABLE RLRP_Players; SELECT \''


The backslashes stop the single quotes from being parsed by MySQL and make them get treated as part of the input.

This was all typed whilst on lunchbreak so there may be some errors…

Thanks a lot for the help!

I have got most of the stuff working now, however i have one problem in my init.lua file, in the GM:Initialize() function, i have no way of getting the player, such as ply:SteamID etc, i tried using metatable but didnt seem to work, any idea?



function GM:Initialize()
    local metaply = FindMetaTable( "Player" )
	local query1 = databaseObject:query("SELECT * FROM RLRP_Players WHERE SteamID = '" .. metaply:SteamID() .. "'")
	query1.onSuccess = function(q)
		if not checkQuery(q) then
			local query2 = databaseObject:query("INSERT INTO RLRP_Players(SteamID, Money) VALUES ('" .. metaply:SteamID() .. "', " .. 15000 .. ")")
			query2.onSuccess = function(q) print("Created " .. metaply:Name() .. "'s Character") end
			query2.onError = function(q,e) print("Could not create " .. metaply:Name() .. "'s character! (Something went wrong!)") end
			query2:start()
		else
			print( ply:Name() .. " already exists in the database!")
		end
	end
	query1.onError = function(q,e) print("Something went wrong when checking if the user was already in the database.") end
	query1:start()
end


Thanks!

The GM:Initialize() function runs when the gamemode has initialize. You can use this function to load initial data into your gamemode from the database.

You want to use the hook PlayerInitialSpawn which runs when the player first spawns into the server. This has an argument, ply (a Player object) which you can use :SteamID() and other functions on.

Timeline:

  1. The server starts and lua runs
  2. The gamemode starts and the GM:Initialize function runs
  3. Player #1 joins
  4. Player #1 spawns, and PlayerInitialSpawn is called with Player #1
  5. Player #2 joins
  6. Player #2 spawns, and PlayerInitialSpawn is called with Player #2

You don’t want to load the money in GM:Initialize because there are no players in the server yet!

You should not use metatables at all, because you are confusing yourself with how they are used.

Ok thanks!

[editline]6th April 2016[/editline]

Any idea whats wrong here?

Client “Alex” connected (25.51.237.178:27006).
Created Alex’s Character

[ERROR] gamemodes/realliferoleplay/gamemode/sv_modules/system_payday.lua:7: attempt to call method ‘AddMoney’ (a nil value)

  1. unknown - gamemodes/realliferoleplay/gamemode/sv_modules/system_payday.lua:7

Timer Failed! [SalaryTimer][@gamemodes/realliferoleplay/gamemode/sv_modules/system_payday.lua (line 4)]

system_payday.lua



include("system_money.lua")
local jobUpdateTime = 10

timer.Create( "SalaryTimer", jobUpdateTime, 0, function()
	for k,v in pairs(player.GetAll()) do
		if v:Team() == 0 then
			v:AddMoney(v:SteamID(), 50)
			chat.AddText( Color( 100, 100, 255 ), "[Payday] ", Color( 100, 255, 100 ), "You've received $50 for being a citizen!" )
		end
	end
end)


system_money.lua:



include("sv_database.lua")

function AddMoney( steamid, amount )
	
	if type(amount) == "number" then
	
		local addmoneyquery = databaseObject:query( "UPDATE RLRP_Players SET `Money` = `Money` + " .. amount .. " WHERE `SteamID` = '" .. steamid .. "'" );
		
		addmoneyquery.onSuccess = function( q )
			
		end
	else
	print("The value needs to be a number! (AddMoney)")
	end
end


On that last one you’re calling v:AddMoney() which is trying to run the AddMoney() method on the Player metatable. As you’ve not declared AddMoney() in that metatable, it’ll be a nil value.

Instead, just do:



AddMoney( v:SteamID(), 50 )


zzaacckk, how come MetaTables shouldn’t be used now? I’ve been away from Gmod Lua for years and we used to use them a lot “back in the day”.

Haha thank you, can’t believe i even missed that… ehh i’m stupid! Thanks a lot man!

[editline]6th April 2016[/editline]

Hey man! I got another question, i need to make a hud at some point, how would i go getting the amount of money from the database?

Obviously it requires selecting it, but how would i put it in a variable
such as
local PlayerMoney = sql code here to get the money

Also, when i open phpmyadmin, the money value doesnt seem to update? It stays at 15000 however i get the chat messages [Payday] You’ve received $50 for being a citizen of paralake city! every 10 seconds. And i get no errors in console.

Picture of phpmyadmin:

system_money.lua



include("sv_database.lua")

function AddMoney( steamid, amount )
	
	if type(amount) == "number" then
	
		local addmoneyquery = databaseObject:query( "UPDATE RLRP_Players SET `Money` = `Money` + " .. amount .. " WHERE `SteamID` = '" .. steamid .. "'" );
		
		addmoneyquery.onSuccess = function( q )
			
		end
	else
	print("The value needs to be a number! (AddMoney)")
	end
end


system_payday.lua



include("system_money.lua")
local jobUpdateTime = 10

timer.Create( "SalaryTimer", jobUpdateTime, 0, function()
	for k,v in pairs(player.GetAll()) do
		if v:Team() == 0 then
			AddMoney(v:SteamID(), 50)
			--chat.AddText( Color( 100, 100, 255 ), "[Payday] ", Color( 100, 255, 100 ), "You've received $50 for being a citizen!" )
			v:PrintMessage( HUD_PRINTTALK, "[Payday] You've received $50 for being a citizen of paralake city!")
		end
	end
end)


Metatables absolutely could (and should, in a good implementation) be used, but the OP was using them incorrectly and it’s probably going to cause more confusion for him right now than it’s worth.

@xAl3xTh3K1nG

  1. In your AddMoney function, you need to execute your query by running addmoneyquery:start()

  2. In order to draw the HUD, there are some concepts you need to understand that you might already know.

There are 3 general spots where data needs to be stored:
1. The database stores the money
2. The server needs to know how much money everyone has
3. Each person (client) needs to know how much money they have

Right now, only your database knows how much money each person has. You need to first make sure the server knows how much money everyone has. To do this, you need to get the amount of money the person has when they first join, then update this variable everytime the database updates (basically when the AddMoney function is called). You should store this in a variable called ply.money or something.

Once the server knows how much money a person has, the server needs to tell the client how much money they have. This is a process called “networking” – this is because you are sending information from one computer (not really, but lets pretend) to another. There are many ways to network information in Garry’s Mod, but there is a really simple way which is SetNWInt. Basically, you set a value with the player, and the server sends it to them automatically and keeps it updated for you. Here is how you can use it.

Server:
[lua]
function getPlayerMoney( ply )
local amount = 50000;
// database query here to get the money
ply:SetNWInt( amount )
end
[/lua]

Client:
[lua]
hook.Add( “HUDPaint”, “DrawMoney”, function()
surface.SetTextColor( 255, 255, 255 ); // set the font color to white
surface.SetTextPos( 75, 10 ); // draw text at the screen position x=75, y=10
surface.DrawText( “Money: $” … LocalPlayer():GetNWInt( “money” ) ); // Draw the text “Money: $50000” or whatever
end );
[/lua]

Remember when I said you should store money in a variable called ply.money or something? Well, you can actually use the function ply:GetNWInt( “money” ) instead of ply.money, assuming they are always the same.

Essentially: ply:GetNWInt( “money” ) is a better version of ply.money in the sense that it sends that variable to all clients on the server, even when they connect after it has been set, and it keeps it up to date with everyone when it gets changed. Be warned though, this is a lot of work for the server so you don’t want to do it often.

Warning: You don’t want to call getPlayerMoney a lot because it does a database query, instead you want to get the variable (with ply:GetNWInt( “money” ) or ply.money).