Page 1 of 1

STILL NEED HELP! If you only see four replies to this post!!

Posted: Wed Nov 13, 2002 3:28 pm
by MeOnTheW3
Very strange stuff here.

I created a few "polling" sites and am having a strange problem over each. I have an IP-checking logic that tests to see if user at IP has voted previous; if not, user's vote is logged.

Now the strange stuff. I am, at random, getting double entries simultaneously. <sp?> Every so often, a person's vote is logged exactly twice, and so is their IP, both double entries are in separate tables. The "auto-increment" id's are incremented properly and the datetime col, instantiated with NOW(), has the same datetime right down to the second. I have tried to repeat this on my own, (ie. double clicking, returning to vote after i have voted then changed my IP) I can not seem to cause the error to happen consistantly, I can, however, cause the double entry every so often, seemingly at randomly.

Now, I know that using:
$sql = "insert or update stmt...";
$result = mysql_query($sql);
if($result)...
can cause duplicate entries when you do the "if" statement, seemingly becuase $result is being reinstantiated on first-use.


My code:

$sql = "INSERT INTO poll VALUES('',".$opt_1.",".$opt_2.",NOW())";
mysql_query($sql) or die('poll insert error: '.mysql_error());
mysql_free_result();

$sql_ip = "INSERT INTO ip_addys VALUES('','".$ip."',NOW())";
mysql_query($sql_ip) or die('ip insert error: '.mysql_error());
mysql_free_result();

The id column is the first col; it is left to mySQL to determine its value.

I know a "good" fix would be to SELECT the MAX(id)+1 and use that in my INSERT to utilize the primary-keys UNIQUE feature. But this would cause me to change alot of code on a highly used site. I am looking for a "real" fix, as these voting sites are being hit hundreds of times a day.

:?: :?: Help, anyone? :?: :?:

Posted: Wed Nov 13, 2002 3:39 pm
by volka
do you see the IP as unique value (for all time)?
If so you may mark the db-field as unique and try a REPLACE-query.
A hotfix, I know, but maybe a quick one ;)

Posted: Wed Nov 13, 2002 3:56 pm
by MeOnTheW3
I'll try that, see what errors show up if it does.

Still need a solid answer on why this is happening, and what to do in future simular programming ventures.

Posted: Thu Nov 14, 2002 9:15 pm
by hedge
It's a little hard to tell without knowing your logic for testing the ip for duplicates. What may be happening is they are pressing the submit button more than once and causing the duplicates.

How about combining the insert and the check, not sure what db you are using but with Oracle it can be done in 1 step:

Insert into tmp (opt1, opt2, ip)
select $opt1, $opt2, $ip
from dual
where $ip not in (select ip from tmp);

Posted: Fri Nov 15, 2002 10:54 am
by MeOnTheW3
I am using php 4.2, mySQL 3.23.52, and my logic flows as follows:

Code: Select all

$sql = "INSERT INTO poll VALUES('',".$opt_1.",".$opt_2.",NOW())"; 
mysql_query($sql) or die('poll insert error: '.mysql_error()); 
mysql_free_result(); 

$sql_ip = "INSERT INTO ip_addys VALUES('','".$ip."',NOW())"; 
mysql_query($sql_ip) or die('ip insert error: '.mysql_error()); 
mysql_free_result();

I have tried the double clicking, does not cause the error.
I have tried refresh, does not cause the error.
I have tried hitting vote then esc then vote, does not cause the error.

I have been able to cause the error randomly, never consistantly, and seemingly when using the system as intended.

Does not make sence.