Page 1 of 1
Personal Hit Counter - how has it gone so wrong?
Posted: Wed May 04, 2011 2:46 am
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?
Re: Personal Hit Counter - how has it gone so wrong?
Posted: Wed May 04, 2011 11:45 am
by McInfo
Re: Personal Hit Counter - how has it gone so wrong?
Posted: Fri May 06, 2011 9:30 am
by simonmlewis
Describe it? In what way?
Field names, field types??
Re: Personal Hit Counter - how has it gone so wrong?
Posted: Fri May 06, 2011 1:03 pm
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.
Re: Personal Hit Counter - how has it gone so wrong?
Posted: Fri May 06, 2011 1:50 pm
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;
Re: Personal Hit Counter - how has it gone so wrong?
Posted: Fri May 06, 2011 2:08 pm
by McInfo
That's even better.