Some tmysql and lua questions

Im fairly new to using mysql, i’ve used it in php but its been a bit of time so i wanted to explore it with lua. I wanted to make a client side script where the user runs a commands, it connects to the database, they get a reward in money based on a value in the database and then it sets another value to 1 making it unusable.


function Retrieve()
	require('tmysql')
	
	tmysql.initialize(localhost, root, password, 3306) --not real info...
	
end

concommand.Add( "retrieve", Retrieve )

I know i would need to run a check in the database to see if the used == 1 or 0 and if it equals 0 then it allows the user to get the reward.

So basically all i need is a little info on tmysql.

An example of how the database looks


CODE | VIP | USED
1234 | 5 | 0

Once they run the command retrieve in the console like this


retrieve 1234

The database would now look like this


CODE | VIP | USED
1234 | 5 | 1

And since the used column == 1, when they run


retrieve 1234

they wouldnt get a reward

For now instead of it giving a reward could we just make it Msg( “you ran the command successfully” ) if the used column == 0 and Msg( “you ran the command and failed” ) if the used column == 1

Sorry that im terrible at explaining and my code is usually always messy, ask question if you need more info
I don’t even know if im allowed to put the tmysql there or if it has to be Initialize function

I’ve never used tmysql, but instead i use mysql-oo.
So i’ll just code it right here how i would do it.

[lua]

// serverside autorun

require(“mysqloo”)

DATABASE_HOST = “localhost”
DATABASE_USERNAME = “root”
DATABASE_PASSWORD = “password”
DATABASE_NAME = “test_db”
DATABASE_PORT = 3306

database = mysqloo.connect(DATABASE_HOST, DATABASE_USERNAME, DATABASE_PASSWORD, DATABASE_NAME, DATABASE_PORT)
database.onConnected = function()
print(“Connected to the mysql-server!”)
end
database:connect()

concommand.Add(“retrieve”,function(ply,_,args)
if args[1] == nil then return end // stop the code if there is no argument

local key = args[1]

local query = database:query(“SELECT * FROM table WHERE code=’”…key…"’ ")
query.onSuccess = function()

if query:getData()[1] == nil then
ply:ChatPrint(“Code was not found!”)
else

if query:getData()[1].used == 0 then

// key was found and it has not been used
// run another query to set the used to 1

local query2 = database:query(“UPDATE table SET used=1 WHERE code=’”…key…"’ ")
query2.onSuccess = function()
ply:ChatPrint(“You have used the code successfly!”)
print(“Key has been used!”)

// give reward

end
query2.onError = function(Q,E) print("An error occured: "…E) end
query2:start()

else
ply:ChatPrint(“Code has already been used!”)
end

end

end
query.onError = function(Q,E) print("An error occured: "…E) end
query:start()

end)

[/lua]

I hope you get how it works, messy code.
I’m not 100% sure it works, didn’t test.

Thanks but ive ran into a small problem whenever i type the command ingame i get this error in the console (serverside)


[lua\autorun\server\vip.lua:50] attempt to index local 'query' (a nil value)

and i guess i should of asked, should i of placed that in the autorun server? if not where then?

I check all the files and everythings in the right place


orangebox/libmysql.dll
orangebox/garrysmod/lua/includes/modules/gmcl_mysqloo.dll
orangebox/garrysmod/lua/includes/modules/gmsv_mysqloo.dll

Thanks for the help though

Hmm…

Are you sure you installed mysql-oo right?
You should try this on server’s console:
lua_run require(“mysqloo”)
and see what it prints.
If it prints ERROR Module not found! then you have installed it wrong,
if it prints nothing it should be installed correctly.

Also, that error occurs when you are not connected to the database.

It just prints


require("mysqloo")...

Then i go to run the command and the same error pops up

When you load the script with lua_openscript, do you get "Connected to the mysql-server! "?
If you don’t, then there is something wrong with the database info. (user,ip,port and password)

alright all the info was correct but i guess i had a bugged out mysqloo or something because i replaced the dlls and it started working

Thank you so much for ur help, exactly what i was looking for!

Quick question though, lets say i use tmysql and mysqloo, would it allow me to connect to 2 different databases using different modules?

You’re welcome.
Yeah i guess it would allow you to connect to 2 different databases, but i think tmysql is a bit old and you should only use mysqloo.

Alright cool, another question, idk if you understand this part but i have this line for a command


hook.Add( "PlayerSay", "CPS1", function( ply, text )
	if string.lower( text ) == "!retrieve" then

end


Is there any way i could set it up where the player types that and it set it all up, i tried adding it in but it just did nothing

it sets what all up? I don’t see anything there…

… how could i put that into the other code to make it check the database and all that stuff and in the end it ply:ChatPrint(“SUCCESS”) if it was successful and the opposite if it wasnt

[lua]
hook.Add( “PlayerSay”, “CPS1”, function( ply, text )
if string.lower( text ) == “!retrieve” then
ply:ConCommand(“retrieve”)
end
end
[/lua]

You are so vague… but I am guessing this?

yes that helps alot i didnt know about that line but how would i set up parameters, i never work with commands