andreaem andreaem - 1 year ago 40
SQL Question

PHP Count site view creating a row every day

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!

Answer Source

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(),?)");

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.