Hit Counter Resetting Itself To Zero At Random

This has been bugging me for a while and I can't think why it's happening. I have a basic hit counter on my site which used to use a flat text file to store the hit count, but the counter was resetting itself, so I changed the script to update a field in a MySQL database, but the counter is still resetting itself seemingly at random. This is the code I'm using:

<?php

$MySQLHost = "localhost";
$MySQLUsername = "****";
$MySQLPassword = "*******";
$MySQLDatabase = "counter";

$MySQLConnection = @mysql_connect($MySQLHost, $MySQLUsername, $MySQLPassword);

if (!$MySQLConnection) {
echo "<p><strong class=\"red\">ERROR:</strong> " . @mysql_error() . ".</p>\n";

}

$MySQLSelectDB = @mysql_select_db($MySQLDatabase, $MySQLConnection);

if (!$MySQLSelectDB) {
echo "<p><strong class=\"red\">ERROR:</strong> " . @mysql_error() . ".</p>\n";

}

$result = @mysql_query("SELECT `hits` FROM `count` WHERE `id` = '1'");

while ($row = mysql_fetch_array($result)) {
$currentcount = $row['hits'];

}

$imagedirectory = "/images/";

$currentcount += 1;
$newcount = $currentcount;
$digits = strlen($newcount);
$actdigits = strlen($newcount);

for ($a = 0; ; $a++) {

if ($a == $digits) {
break;

} else {
$img[$a] = "$imagedirectory";
$img[$a] .= "0.jpg";

}

}

for ($a = 0; ; $a++) {

if ($a == $actdigits) {
break;

} else {
$showdig = substr($newcount, $a, 1);
$img[$digits - $actdigits + $a] = "$imagedirectory";
$img[$digits - $actdigits + $a] .= "$showdig.jpg";

}

}

$displayblock = "";

for ($a = 0; $a < $digits; $a++) {
$displayblock .= "<img src=\"$img[$a]\" alt=\"Counter: $newcount\" />";

}

@mysql_query("UPDATE `count` SET `hits` = '" . $newcount . "' WHERE `id` = '1'");
@mysql_query("INSERT INTO `log` (`hitno`, `logged`) VALUES ('" . $newcount . "', '" . date("d-m-Y H:i:s") . "')");

echo $displayblock;

?>

I added a new table inton the database to log every hit and the time and date so that when the counter resets itself I know exactly when it happens and know what the last count was so I can manually put the count back to what it was, but this is happening five or six times a day and is starting to annoy me. Can anyone shed any light on what might be causing this? Anything wrong with the script?

In case it helpes the server is a Windows 2003 server running IIS 6.0 and PHP 4.3.7 with MySQL 4.0.20a-nt.

Cheers.

Well since I read through this post, I thought I'd take a stab at your problem...it may be longshot, but I wonder if you are having problems with this section:

$result = @mysql_query("SELECT `hits` FROM `count` WHERE `id` = '1'");

while ($row = mysql_fetch_array($result)) {
$currentcount = $row['hits'];

}

$imagedirectory = "/images/";

$currentcount += 1;
$newcount = $currentcount;

Maybe you don;t have the MySQL column type 'hits' set to INT or something like that, so the value of $currentcount results in a string, not a number...

So adding 1 to a string might produce unexpected results? If the string evaluates to the same number everytime, that would explain why it appears like your count is resetting itself....

Try this instead and see if it helps:

$result = @mysql_query("SELECT `hits` FROM `count` WHERE `id` = '1'");

while ($row = mysql_fetch_array($result)) {
$currentcount = $row['hits'];
settype($currentcount, "integer");

}

$imagedirectory = "/images/";

$currentcount += 1;
$newcount = $currentcount;

The hits field is set to BIGINT. This is the structure of the database:

CREATE TABLE `count` (
`id` int(1) NOT NULL default '0',
`hits` bigint(20) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

CREATE TABLE `log` (
`id` bigint(20) NOT NULL auto_increment,
`hitno` bigint(20) NOT NULL default '0',
`logged` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

I take it no one has any suggestions then?

if hits is an integer why are you inserting it into the db as a string? same with the id number fwiw, you're referencing integers as strings.
Presumably the number at which it resets occurs when the string evaluates to 0.

I would change:
@mysql_query("UPDATE `count` SET `hits` = '" . $newcount . "' WHERE `id` = '1'");
to

@mysql_query("UPDATE `count` SET `hits` = `hits`+1 WHERE `id` = '1'");
Theres no need to use your php variable if you're always incrementing by 1 each time.

nice...that eliminates a lot of unnecessary php code and a db query....

tbh I'd probably simplyfy the whole thing (if it does what I think it does)


<?php

@mysql_query("UPDATE `count` SET `hits` = `hits`+1 WHERE `id` = '1'");

$result = @mysql_query("SELECT `hits` FROM `count` WHERE `id` = '1'");

$row=mysql_fetch_array($result);
$hits = $row[0];

@mysql_query("INSERT INTO `log` (`hitno`, `logged`) VALUES ('" . $hits . "', '" . date("d-m-Y H:i:s") . "')");

$digits = preg_split('//', $hits, -1);

$displayblock = "";
foreach($digits as $digit){
$displayblock .= "<img src=\"$imagedirectory$digit.jpg\" alt=\"Counter: $hits\" />";
}
echo $displayblock;

?>

Thanks for the help. I think I figured out the problem just before I saw this message but I'll try out your code Nancy. Cheers.

OK tried it out, however for some reason it outputs the following:

<img src="/images/.jpg" alt="Counter: 103446" /><img src="/images/1.jpg" alt="Counter: 103446" /><img src="/images/0.jpg" alt="Counter: 103446" /><img src="/images/3.jpg" alt="Counter: 103446" /><img src="/images/4.jpg" alt="Counter: 103446" /><img src="/images/4.jpg" alt="Counter: 103446" /><img src="/images/6.jpg" alt="Counter: 103446" /><img src="/images/.jpg" alt="Counter: 103446" />There are two occurences of:

<img src="/images/.jpg" alt="Counter: 103446" />So a broken image is generated at the start and end of the hit count. The complete PHP is now:

<?php

$MySQLHost = "localhost";
$MySQLUsername = "****";
$MySQLPassword = "*******";
$MySQLDatabase = "counter";

$MySQLConnection = @mysql_connect($MySQLHost, $MySQLUsername, $MySQLPassword);

if (!$MySQLConnection) {
echo "<p><strong class=\"red\">ERROR:</strong> " . @mysql_error() . ".</p>\n";

}

$MySQLSelectDB = @mysql_select_db($MySQLDatabase, $MySQLConnection);

if (!$MySQLSelectDB) {
echo "<p><strong class=\"red\">ERROR:</strong> " . @mysql_error() . ".</p>\n";

}

$imagedirectory = "/images/";

@mysql_query("UPDATE `count` SET `hits` = `hits` + 1 WHERE `id` = '2'");
$query = @mysql_query("SELECT `hits` FROM `count` WHERE `id` = '2'");

$rowx = mysql_fetch_array($query);
$hits = $rowx['0'];

@mysql_query("INSERT INTO `log` (`hitno`, `logged`) VALUES ('" . $hits . "', '" . date("d-m-Y H:i:s") . "')");

$digits = preg_split('//', $hits, -1);
$displayblock = "";

foreach ($digits as $digit) {
$displayblock .= "<img src=\"" . $imagedirectory . $digit . ".jpg\" alt=\"Counter: " . $hits . "\" />";

}

echo $displayblock;

?>I adjusted some of the variable names so I could run my current script in parallel to this one so I can continue to check if my current one actually works properly now.

try changing $digits = preg_split('//', $hits, -1);
to $digits = preg_split('//', $hits, -1, PREG_SPLIT_NO_EMPTY);
you can always throw a trim() around hits too just to be on the safe side ;)