IP to Country

Started by vbgamer45, March 06, 2012, 12:01:14 PM

Previous topic - Next topic

vbgamer45

I would drop the ip_lookup and ip_lookup_countries database tables and try to install again.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

LiBen

I Removed both tables, which were empty anyhow.

Hit install, didn't get a warning, waiting...
and succes!  :)

Now updating the old posts via Maintenance => Routine
Now at 7% so I guess it will be a two hour job. To prevent server overload it goes 3 secondes and pauses 3 secondes.  ;D Like a car low on fuel.

Thanks for your advice, vbgamer45!

LiBen

I had to uninstall it!  >:(

Reason: a bug in profile page
index.php?action=profile;area=summary
index.php?action=profile;area=statistics
index.php?action=profile;area=showposts
index.php?action=profile;area=permissions
index.php?action=profile;area=tracking

gives:

syntax error, unexpected ']'

in both:

  • error log
  • on the profile page where <div id="main_admsection"> should be.

It doesn't matter if it's a member or admin looking.   

LiBen

I had to edit modification.xml due to original $sourcedir/Profile-View.php being different than in the xml

Is that the reason?

Adding in pre-mod on line 226 & 228
Adding in post-mod on line 249 & 251



<?xml version="1.0"?>
<!DOCTYPE modification SYSTEM "http://www.simplemachines.org/xml/modification">
<!-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
/**
* @package manifest file for IP to Country
* @version 1.0
* @author Joker (http://www.simplemachines.org/community/index.php?action=profile;u=226111)
* @copyright Copyright (c) 2012
* @license http://www.mozilla.org/MPL/MPL-1.1.html
*/

/*
*
* Version: MPL 1.1
* The contents of this file are subject to the Mozilla Public License Version
* 1.1 (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
* http://www.mozilla.org/MPL/
*
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
* for the specific language governing rights and limitations under the
* License.
*
* The Initial Developer of the Original Code is
* SONSiVRi (http://www.simplemachines.org/community/index.php?action=profile;u=126462)
* Portions created by the Initial Developer are Copyright (C) 2011
* the Initial Developer. All Rights Reserved.
*
* Contributor(s):
* Joker (http://www.simplemachines.org/community/index.php?action=profile;u=226111)
* Helped in updating the mod for SMF 2.x branch
*/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -->
<modification xmlns="http://www.simplemachines.org/xml/modification" xmlns:smf="http://www.simplemachines.org/">
<id>sid2varun:ip_to_country</id>
<version>1.0</version>

<file name="$sourcedir/ManageMaintenance.php">
<operation>
<search position="before"><![CDATA[ 'recount' => 'AdminBoardRecount',]]></search>
<add><![CDATA[
'recountipindex' => 'RecountIPIndex',]]></add>
</operation>
<operation>
<search position="after"><![CDATA[// Perform a detailed version check.  A very good thing ;).]]></search>
<add><![CDATA[// Recount/verify the ip index associated with each message
function RecountIPIndex()
{
global $txt, $context, $smcFunc;

isAllowedTo('admin_forum');
checkSession('request');

// Set up to the context.
$context['page_title'] =  $txt['not_done_title'];
$context['continue_countdown'] = '3';
$context['continue_post_data'] = '';
$context['continue_get_data'] = '';
$context['sub_template'] = 'not_done';
$context['start'] = $_REQUEST['start'];
$context['start_time'] =  time();

// Lets fire the bullet.
@set_time_limit(300);

// Determine the total members with posts.
$request = $smcFunc['db_query']('', '
SELECT COUNT(*)
FROM {db_prefix}messages'
);
list($totalmessages) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);

// Initialize the variables.
$increment = 100;
if (empty($_REQUEST['start']))
$_REQUEST['start'] = 0;

// Grab the set of messages to update.
$request = $smcFunc['db_query']('', '
SELECT id_msg, poster_ip
FROM {db_prefix}messages
ORDER BY id_msg ASC
LIMIT {int:start}, {int:max}',
array(
'start' => $_REQUEST['start'],
'max' => $increment,
)
);

$context['test'] = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$result = $smcFunc['db_query']('', '
SELECT index_number
FROM {db_prefix}ip_lookup
WHERE ip_to >= INET_ATON({string:ip_address})
ORDER BY ip_to ASC
LIMIT 1',
array(
'ip_address' => $row['poster_ip'],
)
);
list ($index_number) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);
$index_number = empty($index_number) ? 0 : $index_number;

// Update the index number
$smcFunc['db_query']('', '
UPDATE {db_prefix}messages
SET ip_index_number = {int:ip_index_number}
WHERE id_msg = {int:id_msg}',
array(
'ip_index_number' => $index_number,
'id_msg' => $row['id_msg'],
)
);
}
$smcFunc['db_free_result']($request);

$_REQUEST['start'] += $increment;

// Move on people
if($_REQUEST['start'] < $totalmessages)
{
$context['continue_get_data'] = '?action=admin;area=maintain;sa=routine;activity=recountipindex;step=0;start=' . $_REQUEST['start'] . ';' . $context['session_var'] . '=' . $context['session_id'];
$context['continue_percent'] = round(100 * $_REQUEST['start'] / $totalmessages);
return;
}
redirectexit('action=admin;area=maintain;sa=routine;done=recountipindex');
}

]]></add>
</operation>
</file>

<file name="$sourcedir/Subs-Post.php">
<operation>
<search position="after"><![CDATA[ // Insert the post.]]></search>
<add><![CDATA[ $result = $smcFunc['db_query']('', '
SELECT index_number
FROM {db_prefix}ip_lookup
WHERE ip_to >= INET_ATON({string:ip_address})
ORDER BY ip_to ASC
LIMIT 1',
array(
'ip_address' => $posterOptions['ip'],
)
);
list ($ip_index_number) = $smcFunc['db_fetch_row']($result);
$smcFunc['db_free_result']($result);
$msgOptions['ip_index_number'] = empty($ip_index_number) ? 0 : $ip_index_number;

]]></add>
</operation>
<operation>
<search position="before"><![CDATA['smileys_enabled' => 'int', 'modified_name' => 'string', 'icon' => 'string-16', 'approved' => 'int',]]></search>
<add><![CDATA[ 'ip_index_number' => 'int',]]></add>
</operation>
<operation>
<search position="before"><![CDATA[$msgOptions['smileys_enabled'] ? 1 : 0, '', $msgOptions['icon'], $msgOptions['approved'],]]></search>
<add><![CDATA[ $msgOptions['ip_index_number'],]]></add>
</operation>
</file>

<file name="$sourcedir/Display.php">
<operation>
<search position="replace"><![CDATA[$messages_request = $smcFunc['db_query']('', '
SELECT
id_msg, icon, subject, poster_time, poster_ip, id_member, modified_time, modified_name, body,
smileys_enabled, poster_name, poster_email, approved,
id_msg_modified < {int:new_from} AS is_read
FROM {db_prefix}messages
WHERE id_msg IN ({array_int:message_list})
ORDER BY id_msg' . (empty($options['view_newest_first']) ? '' : ' DESC'),
array(
'message_list' => $messages,
'new_from' => $topicinfo['new_from'],
)
);]]></search>
<add><![CDATA[$messages_request = $smcFunc['db_query']('', '
SELECT
m.id_msg, m.icon, m.subject, m.poster_time, m.poster_ip, m.id_member, m.modified_time, m.modified_name, m.body,
m.smileys_enabled, m.poster_name, m.poster_email, m.approved,
m.id_msg_modified < {int:new_from} AS is_read, c.country_abbr, c.country_name
FROM {db_prefix}messages AS m
LEFT JOIN {db_prefix}ip_lookup_countries AS c ON (c.index_number = m.ip_index_number)
WHERE m.id_msg IN ({array_int:message_list})
ORDER BY m.id_msg' . (empty($options['view_newest_first']) ? '' : ' DESC'),
array(
'message_list' => $messages,
'new_from' => $topicinfo['new_from'],
)
);]]></add>
</operation>
<operation>
<search position="before"><![CDATA['can_see_ip' => allowedTo('moderate_forum') || ($message['id_member'] == $user_info['id'] && !empty($user_info['id'])),]]></search>
<add><![CDATA[
'country_abbr' => $message['country_abbr'],
'country_name' => $message['country_name'],
]]></add>
</operation>
</file>

<file name="$sourcedir/Profile-View.php">
<operation>
<search position="before"><![CDATA[ $context['last_ip'] = $user_profile[$memID]['member_ip'];
if ($context['last_ip'] != $user_profile[$memID]['member_ip2'])
$context['last_ip2'] = $user_profile[$memID]['member_ip2'];
$context['member']['name'] = $user_profile[$memID]['real_name'];
]]></search>
<add><![CDATA[
// IP to Country
ipToCountry($context['last_ip']);
]]></add>
</operation>
<operation>
<search position="replace"><![CDATA[$request = $smcFunc['db_query']('', '
SELECT poster_ip
FROM {db_prefix}messages
WHERE id_member = {int:current_member}
' . (isset($min_msg_member) ? '
AND id_msg >= {int:min_msg_member} AND id_msg <= {int:max_msg_member}' : '') . '
GROUP BY poster_ip
LIMIT {int:limit}',
array(
'limit' => min($user_profile[$memID]['posts'], 500),
'current_member' => $memID,
'min_msg_member' => !empty($min_msg_member) ? $min_msg_member : 0,
'max_msg_member' => !empty($max_msg_member) ? $max_msg_member : 0,
)
);
$context['ips'] = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
{
$context['ips'][] = '<a href="' . $scripturl . '?action=profile;area=tracking;sa=ip;searchip=' . $row['poster_ip'] . ';u=' . $memID . '">' . $row['poster_ip'] . '</a>';
$ips[] = $row['poster_ip'];
}
$smcFunc['db_free_result']($request);]]></search>
<add><![CDATA[$request = $smcFunc['db_query']('', '
SELECT m.poster_ip, c.country_abbr, c.country_name
FROM {db_prefix}messages AS m
LEFT JOIN {db_prefix}ip_lookup_countries AS c ON (c.index_number = m.ip_index_number)
WHERE m.id_member = {int:current_member}
' . (isset($min_msg_member) ? '
AND m.id_msg >= {int:min_msg_member} AND m.id_msg <= {int:max_msg_member}' : '') . '
GROUP BY m.poster_ip
LIMIT {int:limit}',
array(
'limit' => min($user_profile[$memID]['posts'], 500),
'current_member' => $memID,
'min_msg_member' => !empty($min_msg_member) ? $min_msg_member : 0,
'max_msg_member' => !empty($max_msg_member) ? $max_msg_member : 0,
)
);
$context['ips'] = array();
while ($row = mysql_fetch_assoc($request))
{
$context['ips'][] = '<a href="' . $scripturl . '?action=trackip;searchip=' . $row['poster_ip'] . '" title="' . $row['country_name'] . '">' . $row['poster_ip'] . ' (' . $row['country_abbr'] . ')</a>';
$ips[] = $row['poster_ip'];
}
$smcFunc['db_free_result']($request);]($request);]]></add>
</operation>
<operation>
<search position="before"><![CDATA[ $ip_var = str_replace('*', '%', $context['ip']);
$ip_string = strpos($ip_var, '%') === false ? '= {string:ip_address}' : 'LIKE {string:ip_address}';

if (empty($context['tracking_area']))
$context['page_title'] = $txt['trackIP'] . ' - ' . $context['ip'];
]]></search>
<add><![CDATA[
// IP to Country
ipToCountry($context['ip']);
]]></add>
</operation>
<operation>
<search position="end" />
<add><![CDATA[
// Returns country name and country code of given IP parameter
function ipToCountry($ip_address)
{
global $context, $smcFunc, $settings, $txt;

$request = $smcFunc['db_query']('', '
SELECT c.country_name, c.country_abbr
FROM {db_prefix}ip_lookup as ip
INNER JOIN {db_prefix}ip_lookup_countries as c ON (c.index_number = ip.index_number)
WHERE ip.ip_to >= INET_ATON({string:ip_address})
ORDER BY ip.ip_to ASC
LIMIT 1',
array(
'ip_address' => $ip_address,
)
);

    if ($smcFunc['db_num_rows']($request) > 0)
    {
list ($context['country_name'], $context['country_abbr']) = $smcFunc['db_fetch_row']($request);
$smcFunc['db_free_result']($request);
}
$context['country_name'] = empty($context['country_name']) ? $txt['iptocountry_natitle'] : $context['country_name'];
$context['country_abbr'] = empty($context['country_abbr']) ? $txt['iptocountry_na'] : $context['country_abbr'];
}
]]></add>
</operation>
</file>

<file name="$themedir/Display.template.php">
<operation>
<search position="replace"><![CDATA[ <a href="', $scripturl, '?action=', !empty($message['member']['is_guest']) ? 'trackip' : 'profile;area=tracking;sa=ip;u=' . $message['member']['id'], ';searchip=', $message['member']['ip'], '">', $message['member']['ip'], '</a> <a href="', $scripturl, '?action=helpadmin;help=see_admin_ip" onclick="return reqWin(this.href);" class="help">(?)</a>';
]]></search>
<add><![CDATA[ <a href="', $scripturl, '?action=', !empty($message['member']['is_guest']) ? 'trackip' : 'profile;area=tracking;sa=ip;u=' . $message['member']['id'], ';searchip=', $message['member']['ip'], '" title="', $message['country_name'],'">', $message['member']['ip'], ' (', $message['country_abbr'], ')</a> <a href="', $scripturl, '?action=helpadmin;help=see_admin_ip" onclick="return reqWin(this.href);" class="help">(?)</a>';
]]></add>
</operation>
</file>

<file name="$themedir/Profile.template.php">
<operation>
<search position="replace"><![CDATA[ <a href="', $scripturl, '?action=profile;area=tracking;sa=ip;searchip=', $context['last_ip'], ';u=', $context['member']['id'], '">', $context['last_ip'], '</a>';
]]></search>
<add><![CDATA[ <a href="', $scripturl, '?action=profile;area=tracking;sa=ip;searchip=', $context['last_ip'], ';u=', $context['member']['id'], '" title="' . $context['country_name'] . '">' . $context['last_ip'] . ' (' . $context['country_abbr'] . ')</a>';
]]></add>
</operation>
<operation>
<search position="after"><![CDATA[ // The table inbetween the first and second table shows links to the whois server for every region.
if ($context['single_ip'])
]]></search>
<add><![CDATA[ // IP to Country table, fetches IP with lookup database
echo '
<div class="cat_bar">
<h3 class="catbg">', $context['ip'], ' ', $txt['iptocountry_title'], '</h3>
</div>
<div class="windowbg2">
<span class="topslice"><span></span></span>
<div class="padding">
', $context['country_name'], ' (', $context['country_abbr'], ')
</div>
<span class="botslice"><span></span></span>
</div>
<br />';
]]></add>
</operation>
</file>

<file name="$themedir/ManageMaintenance.template.php">
<operation>
<search position="after"><![CDATA[<div class="cat_bar">
<h3 class="catbg">', $txt['maintain_logs'], '</h3>
</div>]]></search>
<add><![CDATA[<div class="cat_bar">
<h3 class="catbg">', $txt['maintain_recount_ip_index'], '</h3>
</div>
<div class="windowbg">
<span class="topslice"><span></span></span>
<div class="content">
<form action="', $scripturl, '?action=admin;area=maintain;sa=routine;activity=recountipindex" method="post" accept-charset="', $context['character_set'], '">
<p>', $txt['maintain_recount_ip_index_info'], '</p>
<span><input type="submit" value="', $txt['maintain_run_now'], '" class="button_submit" /></span>
<input type="hidden" name="', $context['session_var'], '" value="', $context['session_id'], '" />
</form>
</div>
<span class="botslice"><span></span></span>
</div>
]]></add>
</operation>
</file>

<file name="$languagedir/Modifications.english.php">
<operation>
<search position="end" />
<add><![CDATA[
$txt['iptocountry_title'] = 'to Country';
$txt['iptocountry_na'] = '--';
$txt['iptocountry_natitle'] = 'Not Listed';
$txt['maintain_recount_ip_index'] = 'Created index number of IP\'s associated with message';
$txt['maintain_recount_ip_index_info'] = 'Just use this to regenerate the index number of message IP. Used by the IP to County mod.';
]]></add>
</operation>
</file>

<file name="$languagedir/Modifications.dutch-utf8.php">
<operation>
<search position="end" />
<add><![CDATA[
$txt['iptocountry_title'] = 'naar land';
$txt['iptocountry_na'] = '--';
$txt['iptocountry_natitle'] = 'Niet in de lijst';
$txt['maintain_recount_ip_index'] = 'Gecre&#235;erd indexnummer van IP\'s geassoci&#235;erd met bericht';
$txt['maintain_recount_ip_index_info'] = 'Gebruik dit gewoon om het berichten-IP-indexnummer te regenereren. Gebruikt door de IP naar land mod.';
]]></add>
</operation>
</file>
   
<file name="$languagedir/Modifications.english-utf8.php">
<operation>
<search position="end" />
<add><![CDATA[
$txt['iptocountry_title'] = 'to Country';
$txt['iptocountry_na'] = '--';
$txt['iptocountry_natitle'] = 'Not Listed';
$txt['maintain_recount_ip_index'] = 'Created index number of IP\'s associated with message';
$txt['maintain_recount_ip_index_info'] = 'Just use this to regenerate the index number of message IP. Used by the IP to County mod.';
]]></add>
</operation>
</file>

</modification>


vbgamer45

Yeah if you edited the Proile-View.php edit that could cause the error.

I would need to see the changed file to see the error.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

LiBen

It's in the previous post as code. Editing xml was because the "test before install" in the admin package manager pointed out a fail. And I also had to add dutch-utf8 and english-utf8 for my forum.

I'll place it as attachment. My Proile-View.php is excactly as the SMF 2.0.18 version should be.

vbgamer45

Updated with fixes for 2.0.18 to install.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

LiBen

Installed version 1.0.1 on SMF 2.0.18

Works without errors now. Thank you, vbgamer45

Shades.

Can you update this for 2.1RC4? O:)
ShadesWeb.com - Custom Logos - My Themes on SMF | My Themes on ShadesWeb
https://shadesweb.com

BikerHound.com - Sniffing out the road ahead
https://bikerhound.com

Dream as if you'll live forever; Live as if you'll die today. - James Dean

vbgamer45

Probably for 2.1 final
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

GL700Wing

Just installed this very useful mod - thanks for continuing the development of it!

I noticed that the country information is not shown next to the IP address on the member profile summary screen (which is where I really need to see the country for new members before their application is approved) - fixed as follows:

In ./Sources/Profile-View.php
Find:
$context['can_see_ip'] = true;Add After:

// IP to Country
ipToCountry($context['member']['ip']);

In ./Themes/default/Profile.template.php
Find (a few lines after "if ($context['can_see_ip'])"):
searchip=', $context['member']['ip'], ';u=', $context['member']['id'], '">', $context['member']['ip']Add After:
Find:
. ' (' . $context['country_name'] . ')'
Life doesn't have to be perfect to be wonderful ...

GL700Wing

Another issue I discovered is that the IP Address list is out of date and as a result a lot of posts by my members were shown with a country of 'Not Available' or were attributed to the wrong country.

The list in the mod has just over 158,300 entries while the latest list available from ip2location has almost 209,000 entries.  Also, and to use the latest list from ip2location the country codes list also needs to be updated.

Attached is a zip file containing the updated country_codes.csv and ip_addresses.csv files which I'm now using and after reinstalling the mod with these files and running the maintenance task the IP to Country information in the messages table is now correct.
Life doesn't have to be perfect to be wonderful ...

vbgamer45

Version 1.1
Updated the IP/country database thanks to GL700Wing
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

izotz

Quote from: vbgamer45 on August 29, 2021, 02:08:06 PMProbably for 2.1 final

Any update on this? I understand this takes time. I just want to know what to expect, if there is something we can expect within weeks or months, or if it may not happen at all (which is understandable). Thanks for your help.

GL700Wing

#34
I recently discovered that another update to the IP addresses (ie, the values stored in the ip_lookup table) was necessary when I found the country attributed to posts from a new member was incorrect.

I also discovered that because of the way the previous version of this mod was installed all the data associated with this mod is automatically removed when the mod is uninstalled (this is because the database changes were made using the <code> tag instead of the <database> tag in the package-info.xml file). 

Although this doesn't really matter for the IP address tables it is, in my opinion, an issue for the ip_index_number column in the 'messages' table as it would have to be recreated and re-populated when the mod is re-installed.  I believe this is problematic for following reasons:
1. Due to changes in global IP address allocations/distributions the country association for some existing messages may be different to the country they were previously associated with (eg, with the latest IP address lookup table I found a message associated with Mauritius that was previously associated with Australia); and
2. The process of populating the ip_index_number column in the messages table can take a long time to complete on large forums and it shouldn't be a requirement to have to repeat this process each time the IP address tables are updated.

To address these issues I've modified the package-info.xml file so that:
1. New IP address information can be added via an update; and
2. The option is given to retain the database information when the mod is uninstalled (this would allow for a different version of the the main file that modifies the Sources, Themes or Language files to be installed  by a later release of the mod).

Also attached are the following files:
db-install.php - minor update (drops IP address tables if they exist)
readme_install.txt - updated information (includes IP Address database date).
redirect.txt - new file for redirection after mod install
readme_upgrade.txt - new file for upgrade option (includes IP Address database date).
country_codes.csv - IP address country codes as at 21-Jul-22
ip_addresses.csv.zip - IP Addresses as at 21-Jul-22
Life doesn't have to be perfect to be wonderful ...

vbgamer45

Have updated the mod to 1.2 with @GL700Wing updates!
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

GL700Wing

December 2022 update to ip_addresses.csv ...
Life doesn't have to be perfect to be wonderful ...

GL700Wing

July 2023 update to ip_addresses.csv ...
Life doesn't have to be perfect to be wonderful ...

Advertisement: