Player Merging Query

[killereye]

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:

<?php
//include config & functions from the stats
include_once("stats/includes/config.php");
include_once("stats/includes/functions.php");

// Self join to detect different nicks on the same ip;
$r_ip_result = mysql_query("SELECT DISTINCT(INET_NTOA(u1.ip)) FROM uts_player AS u1, uts_player AS u2 WHERE u1.ip = u2.ip AND u1.pid <> u2.pid ORDER BY INET_NTOA(u1.ip)");

// Output the result to a human readable format
while($r_ip = mysql_fetch_array($r_ip_result)) {
    $r_ip = $r_ip[0];
    $i = 1;
    echo "<br><br><b>$r_ip</b>";
    $r_id_result = mysql_query("SELECT DISTINCT(pid) FROM uts_player WHERE ip = INET_ATON('$r_ip')");
    while($r_id = mysql_fetch_array($r_id_result)) {
        $pid = store($r_id[0]);
        $r_info = small_query("SELECT name, country, banned FROM uts_pinfo WHERE id = '$pid'");        
        $players_ip[$i] = $r_info['name'];
        $pids_ip[$i] = $pid;
        echo "<br>$i: ".$pid.' -> '.$players_ip[$i];
        $i++;
    }
        
}

?>

It will give output like this:

111.111.111.111
1: id1 -> nick1
2: id2 -> nick2

Maybe it's usefull for others too If you want to see more of my nooby hacks, check my previous post.

[Baiter]

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)

[Azazel]

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:

<?
//include config & functions from the stats
include_once("includes/config.php");
include_once("includes/functions.php");

// Query for list of unique ips and player ids
$sql_pipcheck = "SELECT ip, COUNT(DISTINCT pid) AS pidcount FROM uts_player GROUP BY ip";
$q_pipcheck = mysql_query($sql_pipcheck) or die(mysql_error());
while ($r_pipcheck = mysql_fetch_array($q_pipcheck)) {
    
    $playerip = $r_pipcheck[ip];
    $trueplayerip = long2ip($playerip);
    $pidcount = $r_pipcheck[pidcount];
    
    // If there is more than one pid associated to an IP ...
    IF ($pidcount > 1 ) {
    
        echo "$trueplayerip - ";
        
        // Query for player names and ids associated to that ip during the cycle
        $sql_pcheck = "SELECT pi.id, pi.name, pi.country, p.pid, p.ip FROM uts_pinfo AS pi, uts_player AS p WHERE pi.id = p.pid AND p.ip =  $playerip GROUP BY pi.id, pi.name, p.pid, p.ip, pi.country";
        
        $q_pcheck = mysql_query($sql_pcheck) or die(mysql_error());
        while ($r_pcheck = mysql_fetch_array($q_pcheck)) {    
        
            echo '<a class="darkhuman" href="./?p=pinfo&amp;pid='.$r_pcheck[pid].'">'.FormatPlayerName($r_pcheck[country], $r_pcheck['pid'], $r_pcheck['name']).'</a> ';
        }    
        echo '<br /><br />';
    }
}
?>

File goes in the root of utstats

[Baiter]

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!

[Baiter]

Also, can we have the option to CHOOSE which nick that person used the stats will go under?


*waits excited-ly*

[killereye]

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.

[Baiter]

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!

[Azazel]

If I get chance this weekend I'll try and have a look but can't promise anything.

[Baiter]

Originally Posted by Azazel

If I get chance this weekend I'll try and have a look but can't promise anything.

Man, if you can do this, I would be VERY appreciative!

Please do if you have the time!

[killereye]

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.

[Baiter]

bump bump

[killereye]

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

[killereye]

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.

[Baiter]

Nice! Thank you KillerEye.

I'm soo happy to see this thing is finally rollin.

[Azazel]

Nice work killereye, looking good!

[2399Skillz]

Finally! I've had this problem before also!

[killereye]

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:

function merge_players($mplayer1, $mplayer2) {
    $mp1name = small_query("SELECT name FROM uts_pinfo WHERE id = $mplayer1");
    $mp2name = small_query("SELECT name FROM uts_pinfo WHERE id = $mplayer2");
    
    mysql_query("DELETE FROM uts_pinfo WHERE id = $mplayer2") or die(mysql_error());
    mysql_query("UPDATE uts_player SET pid = $mplayer1  WHERE pid = $mplayer2") or die(mysql_error());
    mysql_query("UPDATE uts_weaponstats SET pid = $mplayer1  WHERE pid = $mplayer2") or die(mysql_error());
    mysql_query("DELETE FROM uts_weaponstats WHERE pid = $mplayer2") or die(mysql_error());
    mysql_query("DELETE FROM uts_weaponstats WHERE matchid='0' AND pid = '$mplayer1'") or die(mysql_error());
    
    $q_weaponstats = mysql_query("SELECT weapon, SUM(kills) AS kills, SUM(shots) AS shots, SUM(hits) as hits, SUM(damage) as damage, AVG(acc) AS acc FROM uts_weaponstats WHERE pid = '$mplayer1'  GROUP BY weapon") or die(mysql_error());
    while ($r_weaponstats = mysql_fetch_array($q_weaponstats)) {
        mysql_query("INSERT INTO uts_weaponstats SET matchid='0', pid='$mplayer1',  weapon='${r_weaponstats['weapon']}', kills='${r_weaponstats['kills']}', shots='${r_weaponstats['shots']}', hits='${r_weaponstats['hits']}', damage='${r_weaponstats['damage']}', acc='${r_weaponstats['acc']}'") or die(mysql_error());
    }
    mysql_query("UPDATE uts_match SET firstblood = $mplayer1  WHERE firstblood = $mplayer2") or die(mysql_error());
    mysql_query("UPDATE uts_rank SET pid = $mplayer2 WHERE pid= $mplayer1") or die(mysql_error());
    $sql_nrank = "SELECT SUM(time) AS time, pid, gid, AVG(rank) AS rank, AVG(prevrank) AS prevrank, SUM(matches) AS matches FROM uts_rank WHERE pid = $mplayer2 GROUP BY pid, gid";
    $q_nrank = mysql_query($sql_nrank) or die(mysql_error());
    while ($r_nrank = mysql_fetch_array($q_nrank)) {
    
        mysql_query("INSERT INTO uts_rank SET time = '$r_nrank[time]', pid = $mplayer1, gid = $r_nrank[gid], rank = '$r_nrank[rank]', prevrank = '$r_nrank[prevrank]', matches = $r_nrank[matches]") or die(mysql_error());
    }
    mysql_query("DELETE FROM uts_rank WHERE pid = $mplayer2") or die(mysql_error());
}

The trick is to make a small form around the initial query (see azazel post, that's the good one) and feed that input into this function. If you need to merge multiple nicks, simply merge all nicks to one nick in an iterative way, ie:

for($j=0;$j<count($pid_from);$j++) {
    merge_players($merge_to_pid, $pid_from[$j]);
}

This will merge all pids in the array $pid_from to the pid $merge_to_pid .

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.

[Adrian[ADIES]]

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

[killereye]

Can it do auto merging or just shows which nicks are the same?

Good work anyway!

[Baiter]

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?