Recalculate Flags After New iptocountry Import?

[[BSC]MasterJohnny]

I have just recently updated my UTStats' iptocountry table. Is there any way to 'recalculate' the flags for all players back? (Except for that that the player should rejoin with the same nick.)
Thanks in advance!

[{BL}GypsyAngel[BLA]]

If you held onto your logs backup, you could just upload them to the UT server then reprocess them.

Say, how hard is it to update the IP table? I need to update mine as well.

[[BSC]MasterJohnny]

It's not that hard.

Originally Posted by [es]Rush

1) Get the latest ip2country database from:
http://ip-to-country.webhosting.info...ountry.csv.zip
2) Unzip it
3) Run csvtosql(-linux86/.exe) in the same directory as the .csv file
4) ip-to-country.sql should be created now!
5) Import ip-to-country.sql in mysql/phpmyadmin
TIP: You will have to redo these steps once in a while to keep your database up-to-date!

8) Your QueryServer should be up and running now!

The needed files can be found here.

Then I just renamed the fields to "ip_to", "ip_from" and "country" and deleted the 2 other fields + renamed the table to uts_ip2country after backuping the old one.


So without backupped logs, is it impossible to rebuild the associated flags?

[iDeFiX]

Use an update query to update the country flags instead of reimporting logs. Some basic SQL knowledge required. I'll have a look when I'm home.

[{BL}GypsyAngel[BLA]]

It's very possible, just a little labor intensive.

I suppose if you start by querying for all the records that don't have a country ID listed in it, then pipe that output through a routine that would look up the country ID based on the IP table and fill in the blank, it should work.


I'm a serious noob when it comes to this .php stuff, but I can block out a program in about 30 seconds (I miss AWK).

[Matthew]

I had to learn MySQL queries and the associated PHP workings to make a script that took inventory of accesses and other stuff for work. I think I have a good enough understanding to try and make an update script. From what I can tell, this really doesn't look that hard... just check if the flag is right, and if not, update the table...


EDIT: Though I must say that doing this recalculation could be a MASSIVE strain on databases with very large numbers of players (such as my own).

[[BSC]MasterJohnny]

Thanks for your efforts, good luck with it.

[[BSC]MasterJohnny]

Any news on this?

[Matthew]

It's coming along - just slow

(it's really not that complex, I just don't have a lot of time)

[Azazel]

Ive not even tried this so if someone wants to test, please do it on a backup.

This should make a list of players who have a country of xx and an IP address (xx = country code of people who could not be matched previously).

It then goes through the list, checks the country code again and if there is one, updates the player record with it.

Once again, I've not even tested this so do this on a test/backup

PHP Code:

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

// Query for list of ids where country = xx and player has an IP
$sql_pipcheck = "SELECT id, country, ip FROM uts_pinfo WHERE country = 'xx' AND ip > 0";
$q_pipcheck = mysql_query($sql_pipcheck) or die(mysql_error());
while ($r_pipcheck = mysql_fetch_array($q_pipcheck)) {
    
    $playerid = $r_pipcheck[id];
    $playerip = $r_pipcheck[ip];
    
    // Check the players IP against the country database and update record if a country is found
    $q_playercountry = small_query("SELECT country FROM uts_ip2country WHERE $playerip BETWEEN ip_from AND ip_to;");
    IF($q_playercountry) {
        $playercountry = strtolower($q_playercountry[country]);
        mysql_query("UPDATE uts_pinfo SET country = '$playercountry' WHERE id = $playerid") or die(mysql_error());
    }
}
?>

Copy the above code into a file (say flags.php) and put this file in the root of the utstats folder.

[[BSC]MasterJohnny]

It says:

Unknown column 'ip' in 'field list'

[Azazel]

Indeed, thats what you get for coding after 2 bottles of wine

Try this instead:

PHP Code:

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

// Query for list of ids where country = xx and player has an IP
$sql_pipcheck = "SELECT DISTINCT pid, country, ip FROM uts_player WHERE country = 'xx' AND ip > 0";

$q_pipcheck = mysql_query($sql_pipcheck) or die(mysql_error());
while ($r_pipcheck = mysql_fetch_array($q_pipcheck)) {
    
    $playerid = $r_pipcheck[pid];
    $playerip = $r_pipcheck[ip];
    
     // Check the players IP against the country database and update record if a country is found
    $q_playercountry = small_query("SELECT country FROM uts_ip2country WHERE $playerip BETWEEN ip_from AND ip_to;");
    IF($q_playercountry) {
        $playercountry = strtolower($q_playercountry[country]);
        mysql_query("UPDATE uts_pinfo SET country = '$playercountry' WHERE id = $playerid") or die(mysql_error());
        mysql_query("UPDATE uts_player SET country = '$playercountry' WHERE pid = $playerid") or die(mysql_error());
        echo "Updated Player ID $playerid to country $playercountry <br />";
    }
}
?>

[Azazel]

I've done a quick test of the above and it should work fine ...

[[BSC]MasterJohnny]

Yes, it works, thanks very much!

[Adrian[ADIES]]

Cool Thanks Azazel it worked for me.

[Matthew]

Two bottles Azazel?

Yeah...

[{BL}GypsyAngel[BLA]]

This is sweet. Azazel, you're a god!

Next question, does anyone know how to encode the IPs into the "values" in the SQL file?

Once I know this, I can update the file from my UTStats listing that show anyone without a flag. Take their IP, run it through a reverse IP lookup. That gives me an ISP. I then do a WhoIs to get the entire range that that IP resides in. I get the player's country by asking them. Once I encode it, voila! Anyone else coming in from that IP block is covered.

(if only every ISP would enter their WhoIs data!)

[[BSC]MasterJohnny]

You can use this to convert IPs to decimal:

http://www.kloth.net/services/iplocate.php

[Adrian[ADIES]]

Sorry feels like a double post but seemed better than making a 3rd thread
I also posted the download in Player Merging Query thread.

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

[[BSC]MasterJohnny]

Nice, thanks.