Determining the user's location by IP and creating a hit counter

Hello dear Khabravchane! Having created my site, I added a hit counter from some service (something like qoo ...) to it, 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 people come to me, as well as which country this IP belongs to. In order not to create a bicycle, I decided to take a walk on the forums. I found a lot of different information, including about SxGeo. I liked this option, because there you can immediately find out both the country and the city. Since I am just starting to learn PHP and there are probably a lot of 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 visitors will be entered. I have all the fields except id, go with type varchar 255 and compare utf8_general_ci, for id type int 10 attribute UNSIGNED and of course A / I. Now we will create a configuration file in which we will write down parameters of connection to our database. And finally, download SxGeo.php and SxGeoCity.dat from the sypexgeo.net website links to both files: one and two .


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


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


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, through the getRealIpAddr () function, we get the 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 the value of getRealIpAddr () to $ ip.


$ip = getRealIpAddr();

Now create an SxGeo object and define a 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 data array it will return to you, you may need something else, besides what I suggest. Move on. From this array we select 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 that you visited.


$date = date("H:i:s d.m.Y");        // определяем дату и время события
$host = $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI'];    // определяем страницу сайта

Escape special characters in a string for use in an SQL statement 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 verification for the setup time, then I don’t see the need for it. Now we need to take the values ​​for our hit counter. I took the form of the counter from the same site where the very first counter in the divs inserted the following values:


1. For those who 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 we visualize our table. We create the seestats.php file and connect to our database:


 select_db($db) or die("Не могу подключиться к базе.");
?>

Now a little HTML to bring it all to the screen. I also added a choice of the number of displayed lines.



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


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();
?>

We create a table, styles and sizes you can use which you like:



Теперь через цикл заполняем нашу таблицу и закрываем соединение с БД:


 fetch_array()){
    echo '
'; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; echo ''; } echo ''; echo ''; echo ''; echo ''; echo '
№ п/пВремя и датаДанные о посетителеIP/проксиСтранаРегионГородПосещенный URL
'.$row['id'].''.$row['date'].''.$row['visitor'].''.$row['ip'].''.$row['country'].''.$row['region'].''.$row['city'].''.$row['host'].'
Количество новых посетителей'.$num_rows.'Общее количество посещений'.$res['id'].'
'; echo ''; echo ''; $mysqli -> close(); ?>

Жаль только, что вы будете получать IP прокси-сервера, а не реального клиента.
Спасибо за внимание!


Also popular now: