Duplicate MySQL Inserts and Updates

[Ennyaa]

Hello fellow PHP Freaks!

 

I'm praying that one of you can help me with this very odd problem.  I have a pretty simple script that takes a values passed in through $_REQUEST and updates a field in that id's row by one.  The crazy thing is that sometimes (actually most of the time) it runs more than once.  After the mysql connection is made here is the code in question:

 

if (!empty($cid)) {
	$idQuery = "SELECT id, version, referrals FROM creations WHERE creation_id = '$cid' LIMIT 1";
	if (!($idResult = @mysql_query($idQuery))) {
		// do nothing if query fails
	} else {		
		$rows = mysql_num_rows($idResult);
		// only update referral if the cid isn't bogus
		if ($rows > 0) {			
			$rid = mysql_fetch_array($idResult);
			$ref_id=$rid['id'];
			$tool_id=$rid['version'];
			$visResult = @mysql_query("INSERT INTO visits (ref_id,tool_id) VALUES ($ref_id,$tool_id)");	
		}
	}
	$refQuery = "UPDATE creations SET referrals = referrals+1 WHERE creation_id='$cid'";
	$refResult = @mysql_query($refQuery);
}
}

 

So here's what happens.... if the "referrals" field is set to 0 at first, instead of incrementing to 1 it jumps up to 3.  And then also my visits table gets 3 new entries instead of just one.  BUT (and here's the really weird part)  if the number is higher than 1 it increments properly and inserts only once.

 

It's like my code is being called 3 times the first time around but once on subsequent tries.

 

I have to note that when I'm running this locally with PHP 5.1.6, Apache 2.0, and mySQL 5.0.22 this problem DOES NOT happen.

 

But when I move the code to my webserver that is running PHP 5.0.4, Windows Server 2003, and mySQL 4.1.7  that's when I see this happen.  I know that I need to upgrade my webserver but I'm trying to put that off until I buy new equipment.

 

Can anyone help me with this crazy problem?  Or at least point me in the right direction so I know WHY it's happening?

 

Thanks a lot.

[hitman6003]

Is the referrals column in your table a number type column...INT, BIGINT, SMALLINT etc?  Or is it a VARCHAR or something equally non numeric?

[btherl]

Often these problems are due to browsers or proxy servers making multiple requests.  That's consistent with the problem only occurring in the initial requests.

 

A solution may be to check how you are called.. it's likely that you can distinguish the real and fake requests by looking at the values in $_SERVER.  Possibly you could dump $_SERVER to a file on each request and see what you get, something like:

 

$fp = fopen('/tmp/dump.' . posix_getpid(), 'w');
fwrite($fp, print_r($_SERVER, true));
fclose($fp)

 

If you are being requested multiple times, you will see multiple files created.  You could also check the server logs (if you have access) to see if there are multiple requests.

[Ennyaa]

Hi hitman.  referrals is an int(11) column.

 

btherl... I'm going to try that now.  I'll let you know how it works out.

 

Thanks.

[Ennyaa]

Ok.. it was just like you said btherl (though I had to change your posix call to just time() because it was causing an 'undefined function' fatal error)... it dumped 2 files I guess the question now is what should I check for to make sure the request is a real one?

[Ennyaa]

OK Problem solved... Thank you SO much btherl... w/o you pointing me in the right direction I never would have figured it out.

 

I decided to check on $_SERVER['HTTP_USER_AGENT'].  What was causing the multiple loads was some Google Ad javascript on the page. So the false request user-agent contained the word "Google"  I instructed my script to continue only if that word (along with a few other like 'bot' 'crawl' and 'slurp') were not in the user agent.

 

Thanks again!!