[SOLVED] query not returning results!?
Hello everyone! I have a small problem and I need help.
I'm writing this plugin on a request by a friend of mines. It is supposed to read user privileges from a mysql database and enter them into the admins table from the admin_sql plugin. The plugin should also remove privileges that have expired. The mysql database is filled by a website that I also made, each for a limited period, hence they expire :) When I tested the plugin, it seems to work for the most part, but it makes a new entry into the admins table for each flag that I want to add. It is supposed to add them to existing users with the update query, but it doesn't. I suspect the query in check_privs function doesn't return results, though looking at the code I can't find a reason for that. Anyway, here is the code: PHP Code:
|
Re: query not returning results!?
1. You are using player names in queries without backquoting them (with SQL_QuoteString function). It's fine while testing, but you must fix it or it may cause serious problems if you use it on public server.
2. In handle_user function there is a SELECT query using player id (value 1-32) as a key, which makes no sense to me. 3. Instead of sending 2 queries - first to check password and then getting privs you can do it with just one if you use different table - SELECT * FROM privs WHERE username='%s' AND password='%s';. 4. Instead of messing with strings, you can store all privs as integer. There is tut about operations on bits: http://forums.alliedmods.net/showthread.php?t=139916 5. You didn't post definitions of tables. Are you sure that this query: "INSERT INTO admins VALUES('%s', '%s', '%s', 'k');" inserts everything into proper columns? |
Re: query not returning results!?
1. Thanks for the tip. Will do. Though I don't know how that works :)
2. This was ok when I first wrote it, but after all the modifications I guess I changed it by mistake. Fixed it now: new userID = SQL_ReadResult(Query, SQL_FieldNameToNum(Query, "autoID")) formatex(cache, 511, "SELECT * FROM %s WHERE userID='%d';", g_sqlTable_privs, userID) 3. I can't do that, the password and username is in a different table than the one with the privs. Though maybe I might be able to do it with a more complex query by left joining the two tables, but I'm too lazy to think it up when this way works as well :) 4. Might do that. 5. Yes it should insert it properly. The admins table is created with the admin_sql plugin that comes with amxx. It has auth, password, access and flags fields (eg. user's name, user's pass, abcdefgh..., k) The users table contains users from the website, I believe their fields are pretty self explainatory. The users_privileges table contains the privileges that the users have purchased, with an autoID (primary key auto increment), userID which is the same as autoID from users table, priv id that tells which priv it is, autodate which is timestamp from the purchase moment, and period which is how many days is it purchased for. Hence in handle_priv func: new final_moment = autodate + 86400*period //the timestamp(seconds since epoch launch) + days*seconds a day - gives the moment when the priv expires if(get_systime() > final_moment) remove the priv from user_privileges else add the priv in admins table, where it will be read from the game EDIT: after fixing 2. the problem still exists, just as I thought. after trying to add a and b flags, instead of getting "miromont, topsecret, ab, k" in the admins table, I get "miromont, topsecret, b, k miromont, topsecret, a, k" So either the select query in the check_privs func doesn't return results, or the check for results in handle_check_privs thinks there are no results to update and goes to insert a new line. Or maybe there is even greater problem, dunno. Either way, I can't find the reason for this problem... |
Re: query not returning results!?
The problem may be caused by do while loop. If there is no entry in admins table with auth you are using and then you execute check_privs(Data[0], privID) twice then the result will be obviously 2 inserts.
|
Re: query not returning results!?
That sounds highly probable, but is it me that doesn't understand why, shouldn't the do-while loop execute check_privs, see there is no entry, insert an entry, then the do-while loop moves to the next priv, executes check_privs for it, sees that there is already an entry, and add the second priv with update? At least thats how I thought it would work, but obviously not... Do you have any suggestion how can I make it work the way I want then?
|
Re: query not returning results!?
Do while loop executes check_privs (sends SELECT query), then it moves to the next check_privs (sends SELECT query) and so on. The check to see if there is an entry happens at another time when results are retrieved from database, so the query that inserts data if there are no entries is sent after all SELECT queries from do while loop were sent.
To fix it you can simply concatenate all data that you want to send using do while loop and send it using 1 query. |
Re: query not returning results!?
If I got it right, instead of calling check_privs each time in the loop, I'll move the conversion switch from privID to letter flags up there and save them in a string, then after the loop I'll call check_privs just once and pass the string as data, and after I get result from the select, if there is no such user I'll add it with the whole string of flags, if he exists, I'll just replace his string of flags with the new one. I think this should fix the problem, but it got late, so I'll try it tomorrow :)
EDIT: Here is the code that I got after these changes. I think it looks more readable this way, so it was a good idea to edit it like this. I even added something that I had forgotten to add the first time :) I haven't yet tested if it works now. I'll check now. EDIT 2: just checked it, it does work, at least for the first map. When I change the map, the access field gets updated with gibberish. And I have no idea why. Help please! PHP Code:
|
Re: query not returning results!?
The problem is probably somewhere in handle_priv or in handle_check_privs. If you can't find it while looking at the code, then debug it.
|
Re: query not returning results!?
I think I found the problem. "new len = formatex(access, 31, Data[0])" to "new len = formatex(access, 31, "%d", Data[0])", because it doesn't add the '\0' char the other way :) It should work fine now, I'll test it in a sec.
EDIT: done testing, seems to work fine now. I changed the map 3-4 times with no problems. I'll do some more testing just to make sure it works. Under the post is the final code - I had to change a few more things to accommodate to the change, did some improvements too :) PHP Code:
|
| All times are GMT -4. The time now is 14:29. |
Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.