Personal Hit Counter - how has it gone so wrong?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Personal Hit Counter - how has it gone so wrong?

Post by simonmlewis »

Code: Select all

$todaydate = (date('Y-m-d'));
include "dbconn.php";
$counter = mysql_query ("SELECT * FROM stats WHERE page = 'template' AND date = '$todaydate'");
if (mysql_num_rows($counter)==0) 
  { 
  mysql_query ("INSERT INTO stats (page, date, count) VALUES ('template', '$todaydate', '1')"); 
  }
  else 
  {
  while ($rowcount = mysql_fetch_object($counter))
    {
    $counttotal = $rowcount->count + 1;
    mysql_query ("UPDATE stats SET count = '$counttotal' WHERE id = '$rowcount->id'") or die (mysql_error());
    }
  }
mysql_free_result($counter);
mysql_close($sqlconn);
Average hits per day using a web site companies version show around 103,000 per day, this is each time the template page is hit.
However, this script is making each day show:
04 May 2011 422332
03 May 2011 333421
02 May 2011 108513
01 May 2011 105291
2 May and 1st May, I entered myself. But 3rd and 4th, are from the code. No way was it over 300K yesterday, and this morning in just a few hours, 422k+ ???

What have I done wrong in my code?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Personal Hit Counter - how has it gone so wrong?

Post by McInfo »

Code: Select all

DESCRIBE `stats`;
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Personal Hit Counter - how has it gone so wrong?

Post by simonmlewis »

Describe it? In what way?
Field names, field types??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Personal Hit Counter - how has it gone so wrong?

Post by McInfo »

Describe it the same way MySQL does. I'm mostly interested in field types and indexes so I can determine if it is plausible that the while loop executes more than once. Actually, you shouldn't use a loop if you want to be sure the UPDATE query executes only once.

Here's something else to think about. With a UNIQUE index on `page` and `date` (together), you could trim the script down to just two queries. First, do an INSERT IGNORE that sets `count` to 1. Then, if that query does not affect any rows, do an UPDATE that sets `count` to `count` + 1.
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Personal Hit Counter - how has it gone so wrong?

Post by mikosiko »

+1 with Macinfo UNIQUE index suggestion (just be sure that your date field is DATE type and no DATETIME)... then you can also use just one sql:

Code: Select all

INSERT INTO stats (page, date, count) VALUES ('template', '$todaydate', 1)
       ON DUPLICATE KEY UPDATE count=count+1;
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: Personal Hit Counter - how has it gone so wrong?

Post by McInfo »

That's even better.
Post Reply