PHP Count site view creating a row every day

Question

QUESTION 1: I'm trying to make a script that can count the page view ( on refresh too ) and make a new row on db every day, the I will count in my admin dashboard.

Here is my 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.';
}

I know maybe isn't the correct way to do ( any suggestion accepted ) and this cause every refresh a new row is created instead of update existing one.

QUESTION 2: After saving this values in db, i must to fetch from the table and print in my dashboard, so I'm using a PDO connection and an array_sum (functions convert_to_unit and bd_nice_number convert the number in n K if is 1000 or M if is 1000000)

$siteViewsPrintSQL = $DB_CON -> query("SELECT site_views FROM statistics");
$siteViewsPrint = $siteViewsPrintSQL -> fetchAll(PDO::FETCH_ASSOC);
$siteViewsPrintResult = convert_to_unit(bd_nice_number(array_sum($siteViewPrint)));

This return 0 instead of the sum.

Thanks to all who can help!

Your Common Sense · Accepted Answer · 2016-06-15T05:55:40.047

3

Yes, frankly, this code is just directly opposite to a correct one.

As you are apparently just started learning databases, I would strongly suggest you to go the most basic way, which will be storing every hit. It will make your code dramatically shorter and will let you to learn basic database functions.

So make your table like

dt datetime,
ip varchar(15),

and then on every hit run a query like this

$stmt = $DB_CON->prepare("INSERT INTO stats VALUES (NOW(),?)");
$stmt->execute([$_SERVER['REMOTE_ADDR']]);

and to get the count you will have to run this code

$count = $DB_CON->query("SELECT count(*) FROM stats")->fetchColumn();

This last query is most important: as you can see, a database can count (as well as sum, count averages or do whatever else calculations) for you. So you should never ever do any calculations on the PHP side bu always request the final result from database.

With this database setup you will be able to get your daily traffic, by simply grouping results this way:

$daily = $DB_CON->query("SELECT count(*), date(dt) FROM stats GROUP BY date(dt)")->fetchAll();

And can even get new visitors as well.

edited Jun 15 '16 at 05:55

answered Jun 15 '16 at 05:18

Your Common Sense

156,878
40
214
345

Hi, thanks for the reply, now the question is: using the first code, does it make a new line every time? why when execute the query you pass the REMOTE_ADRESS? ( instead of this I don't want to store IPs for privacy), so I must to delete all my code and use only these two lines? When i count using the query, does it count only the number of row or sum all the values in the rows? – andreaem Jun 15 '16 at 05:24
Yes, it makes an every line every time. This is the point. Every hit counter should store IP. No, it has absolutely nothing to do with privacy. You have to change not only the code, but also the table structure. When you count using the query, it does count the rows, as you may guess. – Your Common Sense Jun 15 '16 at 05:31
ok I've changed the table and delete all the code, in the home I've pasted the first two lines and in my dashboard the $count line, but it doesn't make the insert as I see – andreaem Jun 15 '16 at 05:36
Surelly yes, I've tryed to make it more noob-proof using this $ip = $_SERVER['REMOTE_ADDR']; $now = date("Y-m-d h:m:s"); $stmt = $DB_CON->prepare("INSERT INTO statistics VALUES ($now,$ip"); $stmt->execute(); but still not working, making a var_dump in $stmt return object(PDOStatement)#2 (1) { ["queryString"]=> string(58) "INSERT INTO stats VALUES (2016-06-15 05:06:41,10.240.0.195" } so it's correct, but i can't see the value in the table – andreaem Jun 15 '16 at 05:44
This code won't work for sure as it's entirely wrong. If your insert fails, then you have to get the error message and fix the error. Refer to this answer for the details: http://stackoverflow.com/questions/32648371/pdo-statement-returns-false/32648423#32648423 Note that you have to fix the code I posted above, not your own incorrect approach. – Your Common Sense Jun 15 '16 at 05:48
Understand, i will catch the exceptions later, I've deleted my modification to the code and re pasted yours, but still not working – andreaem Jun 15 '16 at 05:52
So now it's exact time to get (but not catch) the exception and make yourself aware of what's wrong with your code at last. – Your Common Sense Jun 15 '16 at 05:54
By the way, I spotted a typo in my code, a leftover brace, now fixed. – Your Common Sense Jun 15 '16 at 05:56
still not working, i've changed the query in INSERT INTO `andreaem`.`statistics` (`ID`, `dt`, `ip`) VALUES (NULL,'$now','$ip') and now it's working, I know is not the best way to do, but currently store the data as expected, thanks you a lot for your suggestions! – andreaem Jun 15 '16 at 05:59
You don't need an auto oncremented id for this table, it makes no sense – Your Common Sense Jun 15 '16 at 06:06

PHP Count site view creating a row every day

1 Answers1

Linked