PHP - MySQL - INSERT in loop, double entries

Question

I have a very simple loop and for some reason, I see double entries in database. Simple code is below and 1 or 2 out of 1000 INSERT queries, I see double entries. I don’t know why it happens. Is there any MySQL settings or anything that cause delay on INSERT queries and that delay prevents SELECT control to miss it?

for($i = 0; $i < 1000; $i++) {
    $query = mysql_query("SELECT * FROM Table1 WHERE Field1 = '".$i."'");
    if(!mysql_num_rows($query) > 0) {
        $insert = mysql_query("INSERT INTO Table1 SET Field1 = '".$i."'");
    }
}

Can you just insert all values and remove duplicates after that with one query? Or, even better, set the column as having unique values and add IGNORE to the INSERT query. Look at tutorialspoint.com/mysql/mysql-handling-duplicates.htm — Cheery
– Cheery, Commented Oct 20, 2014 at 21:12
It doesn't make sense at all to me. Even if there was a delay in inserts. If you get one value repeated twice, considering that you are inserting the current value stored in $i, if there is a repetition than that would mean that your $i++ did not increment for some reason at some point. Thus it doesn't make sense to me. Perhaps this script is not the culprit to what you think is wrong. Do you have some other code that makes inserts that you can put up? Hopefully I make sense :-) Can you give us an example of what two numbers repeat? — Touch
– Touch, Commented Oct 20, 2014 at 21:38
Cheery, i can do it but it is just extra load for server. I want to know why it happens. — user3741786
– user3741786, Commented Oct 20, 2014 at 23:16
Touch, it is actually long script and it worked without any problem about 6 months and last week, i started to get double entries for some reason. I cleaned out script to find out what part causes problem and finally I simply wrote above code and started testing. it runs every hour and randomly i see double entries. numbers are random too, I see 2 of 54, or 2 of 61. I talked to server tech, they say it might be server overload and interrupts script — user3741786
– user3741786, Commented Oct 20, 2014 at 23:20
@user3741786 it less load than 1 select and 1 possible insert. And your problem could happen if the same code is running in parallel, for example. prepared statements will reduce the load. Also unique will not allow to have duplicates in a column. — Cheery
– Cheery, Commented Oct 20, 2014 at 23:21

Cheery · Accepted Answer · 2014-10-20 21:36:19Z

1

http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

CREATE TABLE Table1
(
   // your columns and definitions
   PRIMARY_KEY(Field1)  // or UNIQUE(Field1)
);

after that, insert ignoring possible errors due to duplicates (no messages, mysql will continue its work)..

for($i = 0; $i < 1000; $i++) {
   mysql_query("INSERT IGNORE INTO Table1 SET Field1 = '$i'");
}

ps: do not use mysql_ extension - it is obsolete, use mysqli_ and it allows to use prepared statements, which make you life and life of MySQL easier as query is prepared and only data is sent in the loop - http://php.net/manual/en/mysqli.quickstart.prepared-statements.php

Like this (it can be written shorter, removing all the checkups)

// setup connection to DB

/* Prepared statement, stage 1: prepare */
if (!($stmt = $mysqli->prepare("INSERT IGNORE INTO Table1 SET Field1 = (?)")))
{
     echo "Prepare failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

/* Prepared statement, stage 2: bind and execute */
$id = 1;
if (!$stmt->bind_param("i", $id)) {
    echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
}

if (!$stmt->execute()) {
    echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
}

/* Prepared statement: repeated execution,
   only data transferred from client to server */
for ($id = 2; $id < 1000; $id++) {
    if (!$stmt->execute()) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }
}

/* explicit close recommended */
$stmt->close();

It looks longer, but less data is sent to the database, reducing its load.

edited Oct 20, 2014 at 21:36

answered Oct 20, 2014 at 21:17

Cheery

16.2k45 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Touch Over a year ago

Sorry to say but I think you are giving a solution to something that is not a problem. Nothing is wrong with the code the OP posted except for using the deprecated mysql_*. Read the comment I provided as a reason above. Maybe I could be wrong.

Collectives™ on Stack Overflow

PHP - MySQL - INSERT in loop, double entries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related