Create a new row every day for counting page views

Question

The basic idea is that I'm trying to make a new row everyday for my page views so that I can echo my daily views.

In my database I have three columns: pageid, pagehits and pagedate (the pagedate table adds the date automatically)

My PHP code looks like this:

<?php
require 'config/db.php';
$today = date("d-m-Y");
$query = "SELECT * FROM pagehits";
$result = mysqli_query($conn, $query);
if ($result->num_rows==0) {
    $insertquery = "INSERT INTO pagehits (pagehits) VALUE (0)";
    mysqli_query($conn,$insertquery);
} else {
    $updateQuery = "UPDATE pagehits SET pagehits = `pagehits` + 1 ORDER BY pageid DESC LIMIT 1";
    mysqli_query($conn,$updateQuery);
}

The problem is now that I want to find out how to insert daily a new row. I also found PHP Count site view creating a row every day with a code where the person has a system to create daily a row specially for counting views but whatever I try to modify my code to make it work too it doesnt work out.

Here is the source code:

$today = date("d-m-Y");
$siteViewsPrintSQL = $DB_CON -> query("SELECT * FROM statistics");
$siteViewsPrint = $siteViewsPrintSQL -> fetch(); 

if ($siteViewsPrint['date'] == $today) {
    $updateSiteViewsCount = "UPDATE andreaem.statistics SET site_views = site_views+1 WHERE date = $today";
    $DB_CON ->query($updateSiteViewsCount);
} elseif ($siteViewsPrint['date'] != $today) {
    $createSiteViewsCount = "INSERT INTO `andreaem`.`statistics` (`ID`, `date`, `site_views`, `new_users`) VALUES (NULL, '$today', '0', '0');";
    $DB_CON -> query($createSiteViewsCount);
    $updateSiteViewsCount = "UPDATE andreaem.statistics SET site_views = site_views+1 WHERE date = $today";
    $DB_CON -> query($updateSiteViewsCount);
} else {
    print 'Error while updating siteviews.';
}

Just add a row whenever a page is requested with a timestamp. You can then query for the sum of requests for any period in time. — sticky bit, Dec 28 '20 at 00:17

score 2 · Accepted Answer · answered Dec 28 '20 at 01:08

As a starter: what many application do is log a row for every hit, rather than increment a daily counter. You would typically log the page name, the timestamp of the hit, and so on.

With this configuration, counting today's hits is just a count(*) query, something like:

select count(*) as cnt_hits
from page_hits
where hit_timestamp >= current_date

Now as for your original question, if you want to maintain a daily counter. You could use a table like so:

create table daily_hits (
    hit_date date primary key,
    cnt int
);

The date is the primary key of the table. We can then use on duplicate key:

insert into daily_hits (hit_date, cnt)
values (current_date, 1)
on duplicate key update cnt = cnt + 1;

This creates a row for today's date with an initial counter of 1; if the row exists already, then the counter is incremented instead.

Create a new row every day for counting page views

1 Answers1