Hit and Online Counter [PHP MySQL]
Note: rename extension to .php to be able to run the script!
How does it Work?
The counters use two database tables hits and online. The first is for counting the total number of visits on a website and the other for showing the number of currently online users. Both counters read the user's IP to distinguish users and the user agent string (provided by the visiting entity) is used to exclude at least some robots (search engines and crawlers). The counters have a time throttle setting to limit the same user from getting more than one hit within a session (defaults to 10 minutes). When the user navigates or reloads the session is extended. If the user is inactive the session will expire after the set time and the user will get a new hit and appear as online again.
How to Setup
The two tables (see the source code below for details) must be created in your MySQL database and the credentials for the database entered in the script - that's basically it. The counters can then be integrated on your website and styled nicely with CSS.
Source Code
Counters.phps
<?php
// ========================================================================
// Hit + Online Counter with PHP and MySQL
// Version: 2019-10-15 08:37:00 (UTC+01:00)
// https://www.dunweber.com/docs/scripts/releases/hitcounters
// Copyright (C) Christian L. Dünweber
// This program is distributed under the GNU General Public License,
// see <http://www.gnu.org/licenses/gpl.html>.
// ------------------------------------------------------------------------
// Prerequisites: Create database tables and enter your credentials below.
// Usage:
// $counters = new Counters();
// echo 'Hits: '.$counters->getHits().'<br />';
// echo 'Online: '.$counters->getOnline();
// ========================================================================
/*
// ------------------------------------------------------------------------
TODO: Create these tables in your MySQL database:
CREATE TABLE `hits` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(20) NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (id)
) AUTO_INCREMENT=1;
CREATE TABLE `online` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ip` varchar(20) NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (id),
UNIQUE (ip)
) AUTO_INCREMENT=1;
// ------------------------------------------------------------------------
*/
class Counters
{
// TODO: Change to your database credentials
private const HOST = 'host';
private const USER = 'username';
private const PASS = 'password';
private const DB = 'database';
// Database table names
private const HITS = 'hits';
private const ONLINE = 'online';
// Time in minutes limiting new hits from the same IP
private const SESSION_LENGTH_MIN = 10;
// The user agent is checked against this list and matches will not get hits or appear online
// TODO: Add more robots as the Internet evolves
private const ROBOTS = ['Ask Jeeves','WebCrawler','findlinks','Gigabot','Googlebot','Google-Sitemaps','HenryTheMiragorobot','libwww-perl','MSNBot','PHP version tracker','PHP_','Researchscan','Scooter','Speedy Spider','SurveyBot','Validator.nu','W3C_'];
private $mysqli;
private $ip;
private $agent;
// ====================================================================
// Constructor: connect to db and init.
// --------------------------------------------------------------------
public function __construct()
{
$this->mysqli = new mysqli(Self::HOST, Self::USER, Self::PASS, Self::DB);
if ($this->mysqli->connect_errno) {
exit('Could not connect to db: '.$this->mysqli->connect_errno);
}
$this->ip = $_SERVER['REMOTE_ADDR'];
$this->agent = $_SERVER['HTTP_USER_AGENT'];
}
// ====================================================================
// Destructor: disconnect from db.
// --------------------------------------------------------------------
public function __destruct()
{
$this->mysqli->close();
}
// ====================================================================
// Get hit counter, i.e. total number of hits.
// --------------------------------------------------------------------
public function getHits(): int
{
// Exclude robots
if (!$this->isRobot($this->agent)) {
// New hit or revisit within session
$result = $this->mysqli->query('SELECT id FROM '.Self::HITS.' WHERE ip=\''.$this->ip.'\' AND DATE_ADD(time, INTERVAL '.Self::SESSION_LENGTH_MIN.' MINUTE) > NOW()');
if ($result) {
$row = $result->fetch_assoc();
// A new hit gets inserted
if (!$row) {
$this->mysqli->query('INSERT INTO '.Self::HITS.' SET time=NOW(), ip=\''.$this->ip.'\'');
}
// Update timestamp for active user
else {
$this->mysqli->query('UPDATE '.Self::HITS.' SET time=NOW() WHERE ip=\''.$this->ip.'\' AND id='.$row['id']);
}
$result->close();
}
}
// Total number of hits are the number of entries in the table
$result = $this->mysqli->query('SELECT COUNT(*) AS hits FROM '.Self::HITS);
if ($result) {
$row = $result->fetch_assoc();
$result->close();
return intval($row['hits']);
}
return -1; // Error
}
// ====================================================================
// Get online counter, i.e. number of currently online.
// --------------------------------------------------------------------
public function getOnline(): int
{
// Exclude robots
if (!$this->isRobot($this->agent)) {
// Is user already present
$result = $this->mysqli->query('SELECT 1 FROM '.Self::ONLINE.' WHERE ip=\''.$this->ip.'\'');
// Update timestamp for active user
if ($result && $result->num_rows) {
$this->mysqli->query('UPDATE '.Self::ONLINE.' SET time=NOW() WHERE ip=\''.$this->ip.'\'');
$result->close();
}
// A new onliner gets inserted
else {
$this->mysqli->query('INSERT INTO '.Self::ONLINE.' SET time=NOW(), ip=\''.$this->ip.'\'');
}
}
// Inactive user are deleted from the db
$this->mysqli->query('DELETE FROM '.Self::ONLINE.' WHERE DATE_ADD(time, INTERVAL '.Self::SESSION_LENGTH_MIN.' MINUTE) < NOW()');
// Number of onliners are the number of entries in the table
$result = $this->mysqli->query('SELECT COUNT(*) AS online FROM '.Self::ONLINE);
if ($result) {
$row = $result->fetch_assoc();
$result->close();
return intval($row['online']);
}
return -1; // Error
}
// ====================================================================
// Check if given user agent advertise to be a robot.
// --------------------------------------------------------------------
private function isRobot(string $agent): bool
{
for ($i = 0; $i < count(Self::ROBOTS); $i++) {
if (stripos($agent, Self::ROBOTS[$i]) !== false) {
return true;
}
}
return false;
}
}
?>