Welcome to lark3ri.fi

Visitor counter with PHP and MySQL, Version 2

19.09.2020

Here is a second version of the previous one. A feature has been added to this version that it will re-register the same IP address after 86400 seconds which is 24 hours.

EDIT: 25.09.2020: Improved how to fetch the total number of visitors. Added hash. Ip-addresses are not visible anymore in the database.

sqlcommand2.sql
CREATE TABLE IF NOT EXISTS `ip` (
	ip VARCHAR(100) NOT NULL
);
CREATE TABLE IF NOT EXISTS `temp` (
	ip VARCHAR(100) NOT NULL,
	date VARCHAR(100) NOT NULL,
	UNIQUE KEY unique_ip(ip)
);

phpvisitorcounter2.php
<?php
$ip_counter_value = "00000000";
$server_name = "localhost";
$user_name = "user";
$password = "password";
$db_name = "ip-addresses";

$conn = mysqli_connect($server_name, $user_name, $password, $db_name);
if($conn !== false) {
	$client_ip = htmlspecialchars($_SERVER['REMOTE_ADDR'], ENT_QUOTES);
	$client_ip = mysqli_real_escape_string($conn, $client_ip);
	$client_ip = hash("sha256", $client_ip);
	$date = mysqli_real_escape_string($conn, time());

    $sql0 = "INSERT INTO `temp`(`ip`, `date`) VALUES ('"
			. $client_ip
			. "', '"
			. $date
			. "')";
	$sql1 = "INSERT INTO `ip`(`ip`) VALUES ('*')";

	if (mysqli_query($conn, $sql0)) {
		mysqli_query($conn, $sql1);

	} else {
		$sql = "SELECT `date` FROM `temp` WHERE `ip` = '" . $client_ip . "'";
		$res = mysqli_query($conn, $sql);
		$row = $res->fetch_assoc();

		if ($date - $row["date"] >= 86400) {
			$sql = "UPDATE `temp` SET `date` = '"
					. $date
					. "' WHERE `ip` = '"
					. $client_ip
					. "'";
			mysqli_query($conn, $sql);
			mysqli_query($conn, $sql1);
		}
	}

	$sql = "SELECT COUNT(*) AS count FROM `ip`";
	$res = mysqli_query($conn, $sql);
	mysqli_close($conn);
	$data = mysqli_fetch_assoc($res);
	$row_count = $data["count"];
	$ip_counter_value = str_pad($row_count, 8, "0", STR_PAD_LEFT);
}
?>

EDIT: 25.09.2020: Code how i converted the database so that all IP addresses are hashed.

phpvisitorcounter2-convertcode.php
<?php
$server_name = "localhost";
$user_name = "user";
$password = "password";
$db_name = "ip-addresses";

$conn = mysqli_connect($server_name, $user_name, $password, $db_name);
if($conn !== false) {
	$sql = "SELECT `ip` FROM `temp`";
	$res = mysqli_query($conn, $sql);
	while ($row = mysqli_fetch_array($res, MYSQLI_NUM)) {
		$ip = $row[0];
		$ip_hash = hash("sha256", $ip);
		$sql = "UPDATE `temp` SET `ip` = '" . $ip_hash . "' WHERE `ip` = '" . $ip . "'";
		mysqli_query($conn, $sql);
	}
	$sql = "SELECT `ip` FROM `ip`";
	$res = mysqli_query($conn, $sql);
	while ($row = mysqli_fetch_array($res, MYSQLI_NUM)) {
		$ip = $row[0];
		$sql = "UPDATE `ip` SET `ip` = '*' WHERE `ip` = '" . $ip . "'";
		mysqli_query($conn, $sql);
	}
	mysqli_close($conn);
}
?>
« Back | ↑ Top