Determine the user's location by IP and create a hit counter

Hello dear Habravchane! Having created my website, I added a visitor counter to it from some service (something like qoo ...), it worked with JS. And often updated. I could not see how many visitors actually were on the site. I also wanted to know from which IP-addresses to me, as well as to which country this IP belongs to. In order not to create a bike decided to walk on the forums. Found a lot of different information including about SxGeo. I liked this option, because there you can immediately find out the country and the city. Since I am just starting to learn PHP and there are probably many such people, I decided to post this article. Only not a piece of code responsible for determining the country and city, but a fully working code. So, let's begin.

First you need to create a table in the database where the data about the visitors will be entered. I have all the fields except id, come with the type varchar 255 and comparison utf8_general_ci, for id the type int 10 is the attribute UNSIGNED and of course A / I. Now we will create a configuration file in which we will write the connection parameters to our database. Finally, download SxGeo.php and SxGeoCity.dat from the sypexgeo.net website for links to both files: one and two .

In SxGeo.php on line 55, when accessing the library, you must replace the file name from SxGeo.dat with SxGeoCity.dat. All the preparatory work is over.

Create a file stats.php (the name of the file of your choice) and establish a connection with our database in it:

<?php include_once ('config.php'); include_once ('SxGeo.php'); $mysqli = new mysqli($server, $user, $pswd, $db); if ($mysqli->connect_errno) { printf("  : %s\n", $mysqli->connect_error); exit();} $mysqli -> select_db($db) or die ("  $db"); if (!$mysqli->set_charset("utf8")) { printf("     utf8: %s\n", $mysqli->error); exit();} 

Now, using the getRealIpAddr () function, we get an ip-client:

 public function getRealIpAddr() { if (!empty($_SERVER['HTTP_CLIENT_IP'])) //  IP { $ip=$_SERVER['HTTP_CLIENT_IP']; } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])) { $ip=$_SERVER['HTTP_X_FORWARDED_FOR']; } else { $ip=$_SERVER['REMOTE_ADDR']; } return $ip;} 

Next we check if the bot came to visit us:

 if (strstr($_SERVER['HTTP_USER_AGENT'], 'YandexBot')) {$visitor='YandexBot';} //   elseif (strstr($_SERVER['HTTP_USER_AGENT'], 'Googlebot')) {$visitor='Googlebot';} else { $visitor=$_SERVER['HTTP_USER_AGENT']; } 

and pass in $ ip the value of the getRealIpAddr () function.

 $ip = getRealIpAddr(); 

Now we create the SxGeo object and define the city, country and region:

 $SxGeo = new SxGeo('SxGeoCity.dat', SXGEO_BATCH | SXGEO_MEMORY); $result = $SxGeo->getCityFull($ip); 

You can use the var_export () function to see which dataset it returns to you, you may need something else besides what I suggest. Go ahead. Choose from this array the values ​​of the country, region and city. And to free up space, delete the SxGeo object.

 $city = $result["city"]["name_ru"]; $region = $result["region"]["name_ru"]; $country = $result["country"]["name_ru"]; unset($SxGeo); 

Now we determine the date, time and page of the site, which went.

 $date = date("H:i:s dmY"); //      $host = $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']; //    

We screen special characters in the string for use in SQL expressions and put our data in a table.

 $ip = $mysqli->real_escape_string($ip); $date = $mysqli->real_escape_string($date); $host = $mysqli->real_escape_string($host); $region = $mysqli->real_escape_string($region); $country = $mysqli->real_escape_string($country); $city = $mysqli->real_escape_string($city); $visitor = $mysqli->real_escape_string($visitor); if ($query = $mysqli -> query("INSERT INTO `pre_visitors` (date, visitor, ip, country, region, city, host) VALUES ('".$date."', '".$visitor."', '".$ip."', '".$country."', '".$region."', '".$city."', '".$host."')")){ //printf("%d  .\n", $mysqli->affected_rows); } else{ printf("Errorcode: %d\n", $mysqli->errno); }; 

You can turn on the check at the time of adjustment, then I don’t see any need for it. Now we need to take values ​​for our hit counter. I took the form of the counter itself from the same site where the first counter in the divs itself inserted the following values:

1. For those who have logged in for the first time or from another ip-address:

 if ($result1 = $mysqli -> query("SELECT * FROM `pre_visitors` WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id'")){ //printf("%d  .\n", $result->affected_rows); } $res1 = $result1 -> num_rows; 

2. For the total number of views:

 if ($result = $mysqli -> query("SELECT MAX(id) AS id FROM `pre_visitors` ORDER BY id")){ //printf("%d  .\n", $result->affected_rows); } $res = $result -> fetch_array(); 

Well, close the connection to the database:

 $mysqli -> close(); ?> 

By the way, do not forget to connect our stats.php file to index.php. Now visualize our table. Create a file seestats.php and connect to our database:

 <?php include_once ('core/config_class.php'); $mysqli = new mysqli($server, $user, $pswd, $db) or die("    MySQL."); $mysqli -> select_db($db) or die("    ."); ?> 

Now a bit of HTML to display all this on the screen. I also added a selection of the number of lines to be output.

 <html> <head> <style type='text/css'> td.zz {PADDING-LEFT: 3px; FONT-SIZE: 9pt; PADDING-TOP: 2px; FONT-FAMILY: Arial; } </style> </head> <body> <center> <form action="seestats.php" method="get"> <select name="visualcount"> <option value="10" selected></option> <option value="50">50</option> <option value="100">100</option> <option value="250">250</option> <option value="500">500</option> <option value="1000">1000</option> </select> <input type="button" value=""> </form> 

Next, we process our form, request all fields from our table excluding bots and duplicate ip-addresses, also request MAX id for ending our table.

 <?php $lastid = isset ($_GET['visualcount']) ? ($_GET['visualcount']) : '10'; if ($query = $mysqli->query("SELECT * FROM `pre_visitors` WHERE (visitor NOT RLIKE 'bot') GROUP BY ip ORDER BY 'id' DESC LIMIT $lastid")){ //printf("%d  .\n", $mysqli->affected_rows); } else{ printf("Errorcode: %d\n", $mysqli->errno); }; if ($result = $mysqli -> query("SELECT MAX(id) AS id FROM `pre_visitors` ORDER BY id")){ //printf("%d  .\n", $result->affected_rows); } $num_rows = $query -> num_rows; $res = $result -> fetch_array(); ?> 

Create a table, styles and sizes can use what you like:

 <table width="680" cellspacing="1" cellpadding="1" border="0" STYLE="table-layout:fixed"> <tr align = "center" bgcolor="#eeeeee"> <td class="zz" width="30"><b>№ /</b></td> <td class="zz" width="90"><b>  </b></td> <td class="zz" width="500"><b>  </b></td> <td class="zz" width="100"><b>IP/</b></td> <td class="zz" width="100"><b></b></td> <td class="zz" width="100"><b></b></td> <td class="zz" width="100"><b></b></td> <td class="zz" width="110"><b> URL</b></td> </tr> 

Now through the cycle we fill our table and close the connection to the database:

 <?php while($row = $query -> fetch_array()){ echo '<tr bgcolor="#eeeeee"><td class="zz">'.$row['id'].'</td>'; echo '<td class="zz">'.$row['date'].'</td>'; echo '<td class="zz">'.$row['visitor'].'</td>'; echo '<td class="zz">'.$row['ip'].'</td>'; echo '<td align = "center" class="zz">'.$row['country'].'</td>'; echo '<td align = "center" class="zz">'.$row['region'].'</td>'; echo '<td align = "center" class="zz">'.$row['city'].'</td>'; echo '<td class="zz">'.$row['host'].'</td></tr>'; } echo '<tr bgcolor="#eeeeee"><td class="zz" colspan = "3" ><b>  </b></td>'; echo '<td align = "center"><b>'.$num_rows.'</b></td>'; echo '<td class="zz" colspan = "3" ><b>  </b></td>'; echo '<td align = "center"><b>'.$res['id'].'</b></td></tr>'; echo '</table>'; echo '</center>'; echo '</body></html>'; $mysqli -> close(); ?> 

The only pity is that you will receive the IP of the proxy server, and not the real client.
Thanks for attention!