CODEWITHSUNDEEP
phpmysql
user3741786
user3741786

Reputation: 21

PHP - MySQL - INSERT in loop, double entries

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."'");
    }
}

Upvotes: 2

Views: 579

Answers (1)

Cheery
Cheery

Reputation: 16214

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.

Upvotes: 1

Related Questions