You see, I have a code that I don't solve quite well, and I want to create a graph with representative data of x Online Users, x Offline Users, Total Visits, Total Unique Users, and most recent visits. But I kept docking on online and offline users, and recent visits. How could I achieve it?
This is my graph:
This is my php code, with which I create a field in my database and update it:
<?php
require_once "conn.php";
session_start();
$_SESSSION['id'] = (isset($_SESSION['id'])) ? $_SESSION['id'] : $_SERVER["REMOTE_ADDR"];
$id = $_SESSION['id'];
$_SESSION['time'] = time();
function checkPageName($page){
$sql = "SELECT * FROM ".HITS." WHERE page = :page";
$query = $GLOBALS['db']->prepare($sql);
$query->execute([':page' => $page]);
if ($query->rowCount() == 0){
$sql = "INSERT INTO ".HITS." (page, count) VALUES (:page, 0)";
$query = $GLOBALS['db']->prepare($sql);
$query->execute([':page' => $page]);
}
}
function updateCounter($page){
checkPageName($page);
$sql = "UPDATE ".HITS." SET count = count+1 WHERE page = :page";
$query = $GLOBALS['db']->prepare($sql);
$query->execute([':page' => $page]);
}
function updateInfo($ip){
$sql = "INSERT INTO ".INFO." (ip, status, count, user_agent) VALUES(:ip, :status, :count, :user_agent)";
$query = $GLOBALS['db']->prepare($sql);
$query->execute([
':ip' => $ip,
':status' => 1,
':count' => 0,
':user_agent' => $_SERVER["HTTP_USER_AGENT"]
]);
}
function updateIPCount($ip) {
if ($_SESSION['time'] < time() - 60) {
session_destroy();
$sql = "UPDATE ".INFO." SET status = 0 WHERE ip = :ip";
}else{
$sql = "UPDATE ".INFO." SET status = 1, count = count+1, time = NOW(), user_agent = :user_agent WHERE ip = :ip";
}
$query = $GLOBALS['db']->prepare($sql);
$query->execute([
':ip' => $ip,
':user_agent' => $_SERVER["HTTP_USER_AGENT"]
]);
}
function checkIP($ip){
$sql = "SELECT * FROM ".INFO." WHERE ip = :ip";
$query = $GLOBALS['db']->prepare($sql);
$query->bindParam(':ip', $ip);
$query->execute();
if ($query->rowCount() > 0){
updateIPCount($ip);
}else{
updateInfo($ip);
}
}
updateCounter("MyWeb");
checkIP($id);
?>
In my database, I have 2 tables, one with the page title information and the total visits. And the other with the information of each user, IP, user agent, date of the last connection, times in which it has been connected and the status whether online or offline. This is the structure of my such:
Tabla Hits
id: auto_increment
page_name: MyPage
count: 100
recent: 10
Tabla Info
id: auto_increment
count: 30
estatus: 1 // 1 online / 0 offline
ip: 123.456.789
user_agent: Mozilla
date: 2017-07-27 00:00:00
As you have the code and the tables, it will be difficult to obtain the information you request (and keep it updated).
We start from the following definitions:
We will work with two tables, one for the activity log of the last 24 hours and one for the total activity log.
Table
actividad
:Table
total
:To manage the table
total
, just add this code at the beginning of the script:Now the queries you need to perform to get the data:
Total visits and unique users
Most recent visits
online users
offline users
From the value of the total connected in the last 24 hours, we would subtract the value of those connected during the last 30 minutes.