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

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

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

Post 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? :?: :?:
Last edited by MeOnTheW3 on Sat Nov 16, 2002 12:29 am, edited 5 times in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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 ;)
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

Post 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.
hedge
Forum Contributor
Posts: 234
Joined: Fri Aug 30, 2002 10:19 am
Location: Calgary, AB, Canada

Post 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);
MeOnTheW3
Forum Commoner
Posts: 48
Joined: Wed Nov 13, 2002 3:28 pm
Location: Calgary, AB

Post 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.
Post Reply