MySQL question functions

Alright so this is my code…



	function LMMESTOREUserBanned(ply)
		db:Query( "SELECT * FROM banned", function(result)
			for i=1, #result[1].data do
				if result[1].data*.banned == ply:SteamID64() then
					print("Hey")
					return true
				end
			end
			return false
		end )
	end


Here’s my problem:

everything is working… the print is printing however if i do LMMESTOREUserBanned(ply) it just does not return anything… like its not returning quick enough… What do i do!

Thanks!

Also im using tmysql4 and i do not have much mysql experience so dont yell at me xD thanks again

XxLMM13xXx - you can’t use it like a normal function, as it takes a second to return, so you will never be able to do:



if !LMMESTOREUserBanned(ply) then


As for the solution on checking this as needed and needing the result, I’m not sure of the best way, but maybe others can suggest.

Also, as I mentioned before - don’t use a loop, just search for the player with:



"SELECT COUNT(*) as x FROM banned WHERE banned = " .. ply:SteamID64()


Then the result will come back as x = 1 if they are banned, or x = 0 if they are not. If you’re not deleting the records when they’re unbanned, then do something like



"SELECT COUNT(*) as x FROM banned WHERE banned = " .. ply:SteamID64() .. " AND WHERE time < " .. os.time()


alright so i cant use it in a function… so how would i be able to use this and have other addons be able to call it (global function/hook) thanks!



function LMMESTOREUserBanned( ply, callback )
	db:Query( "SELECT COUNT(*) AS isBanned FROM banned WHERE steam_id_64 = " .. ply:SteamID64() .. " AND expire_time < " .. os.time(), callback )
end

-- Use in code:

hook.Add("PlayerTriedToDoSomething", "BlahBlah", function(ply)
    LMMESTOREUserBanned( ply, function()
        if result[1].data[1].isBanned == 0 then

            -- JUST DO IT!

        end
    end )
end )


When they join run the query and store it. The issue with queries is they’re asynchronous to the code running, this means you’ll have to wait until they’re done to get the result, and in turn any thing that’ll have to check them will have to wait as well.

I think as above it’s better to run the query when you need it, otherwise when you ‘update’ the database, you’ll also have to update a local copy of the variable, which may be more fragile.

ie

[lua]
hook.Add(“PlayerInitialSpawn”, “SetStoreBanned”, function(ply)
db:Query("SELECT COUNT(*) AS x FROM banned WHERE steam_id_64 = " … ply:SteamID64(), function(result)
ply.StoreBanned = result[1].data[1].x == 1
end)
end)

function IsStoreBanned(ply)
return ply.StoreBanned
end
[/lua]

This way the function will return 3 results. If they’re banned then it’ll return true, if not then false, and if the query hasn’t finished then it returns nil.

[editline]23rd April 2016[/editline]

Well rip auto merge

Yeah so? It’s not hard to remember to update the local copy, it’s better than querying for data multiple times when you don’t need to. Plus you should be creating a function to do it, and in that case you’d only have to do it in one place.

Yes, you could do that, but what i meant is if at any point you update the database, you’ll have to update the local variables to. Could work, but to me who uses MySQL more extensively, it would be a clunky way to do it.

Especially if someone outside that server could update the database.

Yeah? Like I said this should be done in a function so you wouldn’t have to rewrite the same thing over and over.

How would it be clunky? To me clunky would be querying a database and making the person wait on a result that’s already known. Plus if this function is ran multiple times over a short span then you’re just waiting network data.

If the data could be updated from another server then I’d implement something to tell the server that it has changed.

I mean take an example, say you wanted to show the amount of money someone had on a hud, say this money could be updated from other servers as well. Would you query the database every frame to see what money they have? Or would you store a local copy that maybe updates every 5 minutes.

That’s obviously not right, you would obviously update the database when the money changes and then send that to the client to keep a local copy. But what you’re suggesting would work for maybe one or to variables, but if you had 100 that needed to be kept up-to-date, the database is your “Data Base”. Keep the info there, and retrieve it when needed. Otherwise you’re doubling the data locations.

I’m just saying, if I stored data in a database, I would never store it in two places at once. It’s just not needed, and, as I said, clunky. Database queries are built to be ran often and can retrieve large amounts of data quickly from multiple sources.

My post above with the query function and callback command is, in my opinion, the best way to do it. I wouldn’t store the data twice in this scenario. If you store it twice, you have to keep two locations up-to-date and this opens up the risk of inconsistent data.

And like I said, if an external source needed to update it, you’d have to, as you said, tell the database to get the new value, which is again just clunky. The database is your central data storage location, if all sources pull from there when needed, you don’t have to have other messages flying around telling locations to pull down the new data, just pull it down when you need it and it’ll always be the most up-to-date.

Notice how I ‘disagree’ with you, rather then calling you dumb. It’s called maturity.

i need to check the database when the player does something… so i cant just have it once…

like i said i need to run this at random points

I just need a way that i can use my function and it searches the database

The function I gave you was synchronous and you could run it anywhere, just make sure you update the local variable when you ban someone. The function @Semajnad gave you was asynchronous and would require a callback.

[editline]24th April 2016[/editline]

Either way is fine, just @Semajnad 's way then you’ll have to use a callback function and implement it somehow. If you use mine then you can just call the function in an if statement and it’ll give you the result, though if updated from somewhere outside of the server then it’d be wrong.

And the first quote was me talking about @Semajnad 's way of doing it

I’ve mentioned my reasons why I don’t agree with bigdogmat’s way - store the data in one place. Then retrieve it when you need it. No need for data duplication. Databases are built for frequent data access.

Otherwise you’d have to query the database on server start a possibly store 100’s of players ban status’ locally - which is a waste if only 3 of those joined that day. Query the database when you need the data.

Im just gonna use this:



hook.Add("PlayerInitialSpawn", "LMMESTORESetStoreBanned", function(ply)
	db:Query("SELECT * FROM banned", function(result)
		for i=1, #result[1].data do
			if result[1].data*.banned == ply:SteamID64() then
				ply.LMMESTOREStoreBanned = true
				return
			end
		end
		ply.LMMESTOREStoreBanned = false					
	end)
	timer.Create("LMMESTORESetStoreBannedTimer_"..ply:SteamID64(), 10, 0, function()
		db:Query("SELECT * FROM banned", function(result)
			for i=1, #result[1].data do
				if result[1].data*.banned == ply:SteamID64() then
					ply.LMMESTOREStoreBanned = true
					return
				end
			end
			ply.LMMESTOREStoreBanned = false					
		end)		
	end)
end)


I know this will work but will it be like too intense on the server?

I see what you’re trying to do now and storing it on the player may make more sense. I was saying avoid pulling down ALL banned users on server start.

Change PlayerInitialSpawn to PlayerAuthed.
Remove the timer, and just update the ply variable and database when they get banned / un-banned.

Also, you need to learn to use MySQL statements to SELECT the data you need. Don’t select all of it then loop through it.


"SELECT * FROM banned WHERE banned = " .. ply:SteamID64()

But as I think you had before, use COUNT(*) as banned FROM banned WHERE banned = " … ply:SteamID64() - then you’ll just get a number 1 back if they are in the table, or a 0 back if not. 1 if banned, 0 if not. That’s assuming you delete the record which isn’t a good idea, so you’d need to have a banned.active column as well.



COUNT(*) as x FROM banned WHERE banned = " .. ply:SteamID64() .. " AND active = 1"


I just relised I’m mixing up two different people I’m helping atm - so I haven’t mentioned COUNT to you yet :stuck_out_tongue:

It all depends on your database structure. How you mark when a ban has expired, etc. stuff like that.