Player Merging Query
A while ago I made a little query script for the stats system to detect nicklamers. It looks for ip's with more than 1 nick linked to it and outputs these, grouped per ip. This is usefull to quickly detect who's nicklaming with what nicks, and merge em. I don't merge em right away automaticly cause of multiple people behind nat etc + some people got dynamic ips, which results in different people with the same ip over a period of time. Obvious nicks from the same people are detected easily this way tho and make this task go a lot faster.
I do not recommend this code for big databases. Our stats database is 6mb atm and it already takes a while to perform this query. It's a quick and dirty self-join query which probably can be optimized a lot. If you got suggestions, please reply here :) The actual code This is the php code to perform the query & output the result. Change the includes if your path is different. PHP Code:
Quote:
|
Man, I would love something like this.
I've only had stats on my server for 2 months, and I already have a database of 31MB. :( So with this one, you can actually choose which players to merge their other alias' with? (Cuz we have many IPs in which we have several people at) I dont mind waiting like 5-10 minutes for this thing to process if thats what your talking about for big databases. Can you compile this into a PHP file with a readme for installation? (So I can host it) |
1 Attachment(s)
killereye, can you test this to see if its any quicker.
Should give a prettier output and hopefully be less stressful on the database. PHP Code:
|
With this, are we given the option on which IP we want to merge the names with and which ones we dont?
You guys have no idea how many hours this would save me a week! |
Also, can we have the option to CHOOSE which nick that person used the stats will go under?
*waits excited-ly* :P |
I've added time execution debug info to both approaches, you can find both php files in this archive: http://cwlserver.net/merger.zip.
Azazel, your approach is a lot faster here, 4 sec vs 40 sec :) Pretty obvious, cause you avoid a costly join, gj! It would be cool if others could benchmark too ;) Baiter, currently the script only shows the people who share the same IP, but it doesn't merge em. It would be neat if you could just check the people you'd like to merge at the output from this little script. Maybe I'll look into that later, but I do not have that much spare time atm (which also explains my sucky implementation heh ;)) Shouldn't be too hard cause the code for merging is pretty grouped already in admin/mplayers.php. Not sure how to enable people to "check" who to merge into who tho. BTW, I'd like to mention that you still have to recalculate rankings after merging, cause it tends to give a lot lower ranking than it should give. |
Bump Bump.
Alaising is getting out of hand on my stats page. It kinda makes the stats useless, cuz a really good player can have 3-4 of his aliases in the top ten. Can we can an official release for this with the above options? If we need to pay you for your time, I think I can arrange something, but this needs to be looked into. Please help if you can! |
If I get chance this weekend I'll try and have a look but can't promise anything.
|
Quote:
Please do if you have the time! :D |
good job azazel :)
I don't have the time to look into it this month, but might be able to help out a bit later. |
bump bump :thumbup:
|
I'll look into it coming week. I can use it for my own server & it's a good small coding project. If it works it'll be a quick & dirty solution again tho, but at least it'll be a starting point :)
|
I got a really dirty solution working atm. It supports:
* Adding ips to an ignore list (we got some people who share inet connections behind nat, you don't want to merge those and it's annoying to have those fill up the screen). * Per IP you can click "merge", which brings you in the next menu where you see all the nicks associated again & you can chose the nick to merge to. It'll merge all nicks to that nick after that. What I want do: * Select boxes to check the names you want to merge at an IP. * Code it a bit nicely. * Maybe add a normal clickable "quick merge" per ip that just merges all the names to the last used nick, which you can just control click in firefox which makes merging unknown nicks a task of a second. It's based on azazel's improved code to retrieve all ip's with multiple id's. It's certainly NOT safe and fool-prove code. I'll probably add the same security measure as with admin pages (pass-key). Screens First screen which shows all ip's with multiple nicks at it. Checkboxes at the left makes you select which ips you want in yer ignore list which will be done if you click "ignore" at the total end of the page (not shown). The "merge - ip" at the bottom of a list of nicks at the ip gets you in the next screen. This screen makes you select to which nick you want to merge. I want to add select boxes here so you can check who you want to merge (with default all check boxes checked). Suggestions for improvements in the UI or at how it works atm? Baiter, can you test it against a copy of your database? I haven't found any problems yet, but I'm sure there are a few. I'll upload the code & instructions tomorrow or at the end of next week. |
Nice! Thank you KillerEye.
I'm soo happy to see this thing is finally rollin. |
Nice work killereye, looking good!
|
Finally! I've had this problem before also!
|
Quick status update:
Haven't had any time lately for it, sorry. I might have a bit time for it in a few weeks, but don't expect it too soon. If anyone else wants to give it a try, just put the merger code from the utstats in a function like this: Code:
function merge_players($mplayer1, $mplayer2) { Code:
for($j=0;$j<count($pid_from);$j++) { The solution I'd like most is to use a seperate database table to store the data from the above query from azazel in that database, where you can also check which IP's you want to ignore in the future for merging and maybe other tags. That way you'll only have to scan all ip's once, check which ones you want to ignore and save that for next time and after that just merge all other ip's. |
I have securely integrated Azazel's
Player Merging Query and Recalculate Flags After New iptocountry Import with the UTSTATS ADMIN page and make them MySQL5 compatible. Here is a download with the slightly modified scripts and install instructions: http://www.adies.net/downloads/utsta...s-admin001.zip |
Can it do auto merging or just shows which nicks are the same?
Good work anyway! |
I tried this on my Stats, and I dont see how this merges =/
Also, I have ALOT of stats.... And I get this after it dispays about 150 Ips and their names: Fatal error: Maximum execution time of 300 seconds exceeded in www.clansda.com\utstats\pages\admin\pip.php on line 26 Any Idea guys? |
All times are GMT +1. The time now is 10:05 PM. |