You are an unregistered user, you can register here
Navigation

Information

Site

Donations
If you wish to make a donation you can by clicking the image below.


 
Go Back   The Unreal Admins Page > Forums > Hosted Forums > UTStats > General Chat

Reply
Thread Tools Display Modes
  #1  
Unread 18th December, 2006, 05:21 PM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default 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:
<?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:
Quote:
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.

Last edited by killereye : 19th December, 2006 at 10:52 AM. Reason: Used [php] tags instead of [code]
Reply With Quote
  #2  
Unread 18th December, 2006, 05:33 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

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)
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #3  
Unread 18th December, 2006, 10:48 PM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

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 ) {
    
        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
Attached Files
File Type: zip pip.zip (685 Bytes, 177 views)
__________________

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins
Reply With Quote
  #4  
Unread 18th December, 2006, 11:50 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

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!
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #5  
Unread 19th December, 2006, 12:26 AM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

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


*waits excited-ly*
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #6  
Unread 19th December, 2006, 11:14 AM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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.

Last edited by killereye : 19th December, 2006 at 11:21 AM.
Reply With Quote
  #7  
Unread 13th January, 2007, 10:32 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

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!
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #8  
Unread 18th January, 2007, 12:04 AM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

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

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins
Reply With Quote
  #9  
Unread 18th January, 2007, 12:23 AM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

Quote:
Originally Posted by Azazel View Post
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!
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #10  
Unread 21st January, 2007, 06:39 AM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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.
Reply With Quote
  #11  
Unread 24th January, 2007, 07:35 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

bump bump
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #12  
Unread 29th January, 2007, 09:04 AM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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
Reply With Quote
  #13  
Unread 1st February, 2007, 02:53 PM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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.
Reply With Quote
  #14  
Unread 1st February, 2007, 04:44 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

Nice! Thank you KillerEye.

I'm soo happy to see this thing is finally rollin.
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
  #15  
Unread 3rd February, 2007, 01:00 PM
Azazel's Avatar
Azazel Azazel is offline
Administrator
 
Join Date: Jul 2002
Location: UK
Posts: 2,408
Default

Nice work killereye, looking good!
__________________

Phoenix Alliance - The Movie
Revisionistic Movie
The Unreal Admins Page - the resource for unreal admins
Reply With Quote
  #16  
Unread 8th February, 2007, 08:40 AM
2399Skillz 2399Skillz is offline
Super Moderator
 
Join Date: Jan 2004
Location: North Carolina
Posts: 2,245
Default

Finally! I've had this problem before also!
__________________

UT-FILES.COM
Get your files
Reply With Quote
  #17  
Unread 27th February, 2007, 07:29 PM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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) {
    $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:
Code:
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.
Reply With Quote
  #18  
Unread 28th May, 2007, 04:10 PM
Adrian[ADIES]'s Avatar
Adrian[ADIES] Adrian[ADIES] is offline
Godlike
 
Join Date: May 2005
Location: UK NorthWest
Posts: 252
Default

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
__________________
www.adies.net - Dedicated to Strangelove-CTF.

Download my UT map CTF-Microhec33 CTF-Microhec33.zip :::: Read The Review
Reply With Quote
  #19  
Unread 29th May, 2007, 10:25 AM
killereye killereye is offline
Dominating
 
Join Date: Sep 2006
Posts: 106
Default

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

Good work anyway!
Reply With Quote
  #20  
Unread 29th May, 2007, 01:28 PM
Baiter's Avatar
Baiter Baiter is offline
Holy Shit!!
 
Join Date: Apr 2004
Location: Houston, TX
Posts: 1,566
Default

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?
__________________
-={SDA}=-Baiter | Maker of Sniper's Heaven
Clan SDA | UT Server Baiter Edition | Play Sniper's Heaven!!! | SDA Gaming Servers
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:12 AM.


 

All pages are copyright The Unreal Admins Page.
You may not copy any pages without our express permission.