INSERT INTO doing double duty

[devknob]

PHP version  5.1.6

MySQL version 5.0.27-standard-log

 

Table is super simple, 1 primary key, uid, unixtime(timestamp),blog, pro, bull, pix

 

if($uid > 0 && $id != $uid){

include('connection.php');

mysql_query("INSERT INTO history SET uid='$uid', pro='$id'"); }

 

Thats all. Does anyone know why this would create two identical records every time?

[severndigital]

not sure why but you can try this to fix it.

 

//change this
mysql_query("INSERT INTO history SET uid='$uid', pro='$id'");

//to this
mysql_query("INSERT INTO history SET uid='$uid', pro='$id' LIMIT 1");

 

that would stop it running itself twice perhaps.

 

chris

[devknob]

???

 

tried it, but since you suggested it and im not finding anything on useful google(whoa), tried it again, same results with the limit, no insert....blah.

[severndigital]

interesting

 

try something really off the wall .. swith the && an || if it works with the logic.

 

let me know,

 

we'll figure something out 

 

chris

[devknob]

http://bugs.mysql.com/bug.php?id=16304

 

jeez, what a lame ass bug

[severndigital]

that sucks.  did you get it working eventually??

 

also, you should mark this topic sovled.

 

thanks,

chris

[soycharliente]

Did you try using the structure that lists the field names and uses the word VALUES?

[Illusion]

try this

INSERT INTO history SET uid='$uid', pro='$id' ON DUPLICATE KEY UPDATE uid=uid+1;

 

 

[fenway]

I don't even understand the issue... but it is resolved?

[devknob]

Not resolved. Tried the syntax with the VALUES, tried the alternative DUPLICATE KEY thing. Nada, still inserting double. I replaced the SQL query with an echo "test" and it only printed to the screen once. Totally stumped

[devknob]

FYI, if I run that INSERT INTO mysql query directly in mysql, it doesnt create 2 records.

[rationalrabbit]

I am having the same problem and I can not figure out why. I didn't see this post, and created one called "Saving Data Twice."

 

I've placed an exit after the save and even cleared the post variables, and it still happens. I believe it is not doubling the declared insert, but rather duplicating the current record in the database. I believe this because, while testing, for a while, rather than saving the same data twice, it was duplicating another current record in the database. I'm sorry I can't give you any more detail than that - I don't know what may have moved the pointer, but it may be an eye opener.

 

That said, and still being uncertain, I need to ask if you are posting to your form file or a different file?

[fenway]

My guess it that the script is running twice...

[rationalrabbit]

My guess it that the script is running twice...

 

That, of course, was my first thought, especially since the file is posting to itself. However, I have tried different things; clear($_POST); header("location: otherfile.php"); exit and finally separated the HTML and PHP into to different files and posted to the PHP file. All these had the same result. I also tried halting the file and waiting for user input directly after the SQL insert, and then physically checking the database before proceeding.  I have taken the code down to the bare bones and only 4 fields and still the same result.

 

I think the problem has to be in the database setup, although it is quite simple:

15 fields; integers; tinyintegers; varchar; date; char & float

1 auto-increment id field as primary

two indexes (first & last name)

 

[devknob]

My guess it that the script is running twice...

 

If I replace the INSERT INTO, with an echo "test"; it only prints test to the screen once. Also if I put the insert into command directly into mysql, it only inserts one time. Its whacky because I have copy and pasted the script into other php files and it only inserts once.

 

ALSO: I am posting to SELF (same file)

 

If noone has a fix for this im assuming its just an error with php. I guess.

[devknob]

Does anyone have a suggestion to work around? I suppose I can insert an entry, then delete the last entry... but thats lame, and would probably have to be fixed once an update happens. All my other INSERT INTO commands work as needed. lame... ???

[rationalrabbit]

devknob -

I spent hours working on this, including a lot of time searching the web for other people with the same problem, or some MySQL guru who might have a clue, and coming up empty handed. I've been told things like "well, if you validate a unique field, you won't have that problem".  True, but I want to find out why the problem is occurring - not hide it and pretend it didn't happen.

 

Currently, the problem is gone.

 

All I can tell you is that I added two fields to the database, turned off auto-increment on my "id" field, moved the primary key to another field (and forgot to remove the primary key from the auto-increment field), saved two records, and there were no duplicate saves.  I then turned auto-increment back on in the id field, removed the primary key from the second field, and all is working fine.

 

At this point, I would say the problem really appears to be a MySQL bug.  If you read my other post ("Saving Data Twice"), you would remember that I have two identical databases (except they are different MySQL versions). I'm swamped with work, and have to go out of town, but next week I will go to the other database, and do a lot more careful investigation. When I am done, I will post my findings here.

[Illusion]

use second query after the insert statement

 

$result1=mysql_query("INSERT INTO history SET uid='$uid', pro='$id'");

$result2=mysql_query("OPTIMIZE TABLE table_name");

echo mysql_affected_rows($result1);

 

 

just to make sure that ur insert statement not executing two times.

[fenway]

Well, if you turn on the mysql logger, you should be able to see if your script it really sending db statements or not.

[rationalrabbit]

I'm embarrassed to say I had not thought of checking logs.

I will have both of your suggestions in mind as I test the second database, which will be a few days yet.

 

Thanks.