HOWTO:[PHP] Advanced Who's Online

This tutorial will show you step by step how to display who's on your website, what page they're viewing, and even what country they are located in! Very cool for webmasters who like to keep an eye on their site's usage.

First we're going to need a few things to actually do this:

1) Download this map package: ">http://www.ip2location.com/flags.zip " title="Visit through proxy">
2) Download this IP database:

" title="Visit through proxy">">http://ip-to-country.webhosting.info/downloads/ip-to-country.csv.zip " title="Visit through proxy">" title="Visit through proxy">
Unpack the flags.zip file into a directory on your website named /flags.

Create a database in MySQL, or if you already have a database created move on to the next step.

Create 2 tables:

Table 1: pageviews
Fields in pageviews: - [field name] [field type/size] [other]
- pageview_id (int) (auto-increment) (primary key)
- ip (varchar 15)
- uri (varchar 255)
- timestamp (timestamp)

Table 2: ip2country
Fields in ip2country:
- iplow (varchar 15)
- iphigh (varchar 15)
- country_2 (char 2)
- country_3 (char 3)
- country_name (varchar 75)

Next, we're going to import the contents of ip-to-country.csv.zip to the ip2country table.

Unzip ip-to-country.csv.zip
Go to PHPMyAdmin and go to the structure view of ip2country.
In the very bottom of the main PHPMyAdmin window you'll see this text link: "Insert data from a textfile into table". click it.

Click the Browse button and select the unzipped ip-to-country.csv file.

Fields terminated by: change to , (comma).

Hit Submit. It may take a few minutes to upload the file, but once done you should have over 40,000 rows in that table.

Now, here's an optimization tip.

Go back to the Structure view of the ip2country table.
Where it says "Create an index on [1 ] columns" Change that to 2, and click go.
For the first field, choose iplow, and for the second field choose iphigh. Click save and you're done.

Okay, now we're moving on to the PHP side of things.


There are going to be a few steps needed to get everything working:

1) Each time someone visits a page we need to make a database entry
2) A function to grab unique ips that were on the site in the last 5 minutes.
3) Display the list of users with flags and all.

I'm going to assume you already have a database link setup, if you don't know go, go read about that here: http://www.php.net/mysql

Step 1: Storing Website traffic into the database.

You'll need to add this code to every page that you want to track users on.

Code:

<?php
$user_ip = $_SERVER['REMOTE_ADDR'];
$curr_page = $_SERVER['REQUEST_URI'];

// lets remove any session info from the url //
if ($_GET[PHPSESSID]) {
   $curr_page = ereg_replace('[&|\?]PHPSESSID=[^&]+','',$REQUEST_URI);
}

// insert the page view into the database //
$query = "insert into pageviews (ip, uri)
   values ('$user_ip', '$curr_page')";
$result = mysql_query($query);
?>

So everytime a user opens a page that has that code, it will create a new entry into the table pageviews with the page they were on, their ip, and the time they viewed the page.

Step 2 & 3: Grabbing users that were on in the last 5 minutes, then displaying them.

Steps 2 and 3 can both happen on the same page, for instance a php file named whos_online.php which would display the user's online.

Code:

<?php
// this gets the unix time of 5 minutes ago //
$maxtime = time() - (60 * 5);

$query = "select distinct ip, max(pageview_id) as pageview_id from pageviews
   where unix_timestamp(create_date) >= $maxtime
   group by ip
   order by pageview_id desc";
$result = mysql_query($query);
?>

<table>
<tr>
<td>Who's online right Now?</td>
</tr>
<td>
<?php
// this function will convert an IP to a country //
function IP2Country($ip) {
        $query = "select country_name from ip2country
                where inet_aton('$ip') between iplow and iphigh";
        $result = mysql_query($query);
        if (mysql_num_rows($result) > 0) {
                extract(mysql_fetch_assoc($result));
        } else {
                $country_name = "UNKNOWN";
        }
        return($country_name);
}

// this function will get the flag for the specified country //
function CountryFlag($ip) {
        $query = "select country_2 from ip2country
                where inet_aton('$ip') between iplow and iphigh";
        $result = mysql_query($query);
        if (mysql_num_rows($result) > 0) {
                extract(mysql_fetch_assoc($result));
                $country_2 = strtolower($country_2);
                $flag_img = "/flags/$country_2.gif";
        } else {
                $flag_img = "/flags/null.gif";
        }
        return($flag_img);
}

while ($row = mysql_fetch_assoc($result)) {
   $ip = $row['ip'];
   $country = IP2Country($ip);
   $flag_img = CountryFlag($ip);

   // split ip into parts so we can hide part of the ip
   ereg("([0-9]*)\.([0-9]*)\.([0-9]*)\.([0-9]*)", $ip, $ip_parts);
   $ip = $ip_parts[1].".xxx.xxx.".$ip_parts[4];

   $pageview_id = $row['pageview_id'];
   $query_page = "select uri from pageviews where pageview_id = $pageview_id";
   $result_page = mysql_query($query_page);
   extract(mysql_fetch_assoc($result_page));
   
   // here we display the flag, hidden ip, their page viewed, and a link to that page //
   print "<img src=\"$flag_img\" width=18 height=12 border=0> $country [$ip] <a href=\"$uri\">$uri</a><br>";
}
</td>
</tr>
</table>

So what just happened up there? Well, first we grabbed only users that had been on the site in the last 5 minutes. Then for each user, we only grabbed the LAST page they viewed.

Next, we had to determine for their IP which country they were located in. We did this through a little function that queried the database, and grabbed the country name.

Then we wanted to get the flag for that country, so we did another query, and made an <img> link to the flag file in the /flags directory where you unpacked all those little flags.

After that, we wanted to hide the user's IP when displaying it.. but not the whole IP, just the middle parts of it. This way malicious users couldn't get any info about the other users on your site. So we changed an IP that was 10.10.10.10 to 10.x.x.10.

Finally, we put it all together in an HTML table and displayed the results.

If you want to show just the number of user's online, and say link from that to the detailed list. You can re-use a lot of that code up there and just do a count instead. I'll let you figure that one out, it's easy!

[Guest]

[37.737]

Googlebot, Baidu Spider, Yahoo crawler, Msnbot, edock, Nikolas, Gigabot