How would I count how many rows were returned in a tMySQL query (with lua)?

Ive got a simple gamemode up and running and ive got a simple system where if a player joins, Their Nick, SteamID and IP are inserted into the database.

What I would like to know is how I would count how many rows were returned in a query.

This is my current code



function GM:PlayerInitialSpawn ( ply )

	tmysql.initialize( "x", "x", "x", "x", 3306)
	
	tmysql.query("SELECT ALL FROM users WHERE sid = '" .. ply:SteamID() .. "'")
	
	tmysql.query("INSERT INTO `users` (`sid`, `name`, `ip`) VALUES ('" .. ply:SteamID() .. "', '" .. ply:Nick() .. "', '" .. ply:IPAddress() .. "')")

	self.BaseClass:PlayerInitialSpawn( ply )
	
	if ply:IsAdmin() then
		ply:PrintMessage(HUD_PRINTTALK, "Welcome to the server.")
	else
		ply:PrintMessage(HUD_PRINTTALK, "Welcome to the server.")
	end
	
	

end


What I want it to do is see if there is already a row with there steamid and if there is, dont do anything. But if there isnt then create one, if that makes sense.

tmysql uses callbacks iirc, so in the query result check if #args == 0 and if not, then query the insert.

Im probably going to sound like such a noob here, But could you give me a code example?

tmysql.query(query,function(ret) if #ret > 0 then – Query insert end end )

[editline]23rd October 2011[/editline]

Not sure if that’s specifically the arguments passed by tmysql, might be different.

So something like this,



tmysql.query(SELECT ALL FROM users WHERE sid = '" .. ply:SteamID() .. "',function(ret) if #ret > 0 then INSERT INTO `users` (`sid`, `name`, `ip`) VALUES ('" .. ply:SteamID() .. "', '" .. ply:Nick() .. "', '" .. ply:IPAddress() .. "')")


[editline]23rd October 2011[/editline]

Also, My server is now refusing to query any data into any SQL Database (I have tried running one on the web which worked fine but now it isnt and locally but both are now not working and I dont have a clue whats wrong.)

Got the operator the wrong way around. == would work instead.

I tried that, didnt work. (It wouldnt insert the rows into the database)



tmysql.query(SELECT ALL FROM users WHERE sid == '" .. ply:SteamID() .. "',function(ret) if #ret > 0 then INSERT INTO `users` (`sid`, `name`, `ip`) VALUES ('" .. ply:SteamID() .. "', '" .. ply:Nick() .. "', '" .. ply:IPAddress() .. "')")


tmysql.query(SELECT ALL FROM users WHERE sid == ‘" … ply:SteamID() … "’,function(ret) if #ret == 0 then INSERT INTO users (sid, name, ip) VALUES (’" … ply:SteamID() … “’, '” … ply:Nick() … “’, '” … ply:IPAddress() … “’)”)

Have you checked what is passed into the callback function? I haven’t, so I can’t guarantee the first argument is the table.

No, I havent nor do I know how to find out.

I might just use gm_mysql since it seems to work a bit better and may be easier to code.

http://gmodmodules.googlecode.com/svn/trunk/gm_tmysql2/readme.txt

Example:
[LUA]
tmysql.query(“SELECT * FROM table WHERE steam_id = '”…tostring(Player:SteamID())…"’",function(res,stat,err)
if(#res < 1) then
//Player dont exist in database
else
//Player DO Exist in the database
end

end,1)
[/LUA]

That was slightly late, I’ve already switched over to gm_mysql and got it to work perfectly (Sorry).

Also, What does [1] next to a variable mean?

Its gathering the value associated with that key (1) in a table.

TableName aka [1]
Index1 = {}
Index2 = {}

[1] is 0 if there is no rows returned, or 1 + depending on the rows

print( #results[1] ) will return the amount of rows from the query.

The length of a table doesn’t return a table.

type(#table) == integer

The fuck are you on about, I said print( #results[1] ) will return ur ROW COUNT

Just so we are clear, results is a table.

Putting ‘#’ infront of a reference to a table will return its length, this is an integer.

You cannot select a specific index of a integer the same way you do by using a table “[key]”.

To get the total row count you would need a SELECT COUNT(*) query if you wanted to be efficient.

OP : I would suggest doing this…

[lua]
local lQuery = string.format(“SELECT COUNT(*) FROM users WHERE sid = %q;”,Player:SteamID());
[/lua]

Then you can do something like tonumber(response['COUNT(*)])) to see the amount of characters they have.