CODEWITHSUNDEEP
phpmysqlcron
LeviZoesch
LeviZoesch

Reputation: 1621

SQL INSERT INTO table results in duplicates

I have a script that I have setup a CRON for that is getting values from a 3rd party server via JSON (cURL)

Right now every time the cron runs it will INSERT a completely new record. Causing duplicates, and resulting me in manually removing the dups.

How would I go about preventing duplicates, and only update the information that is either missing, or different from the NEW $VAR values?

What I want to do can be expressed like this: IF old value is NOT new value use new value else use old value;

    if ($stmt->num_rows !== 1) {
    if ($insert_stmt = $mysqli->prepare("
                                        INSERT INTO members (
                                                            start_date
                                                            )

                                        VALUES (?)")) 

        {
        $insert_stmt->bind_param('s',
                                $StartDate, 

                                );

    if (! $insert_stmt->execute()) { echo ''; }
        }
}
}

Upvotes: 1

Views: 137

Answers (3)

LeviZoesch
LeviZoesch

Reputation: 1621

I ended up writing another if statement to check if a unique value existed from incoming and the existing db value existed and leaving it blank to prevent it from importing duplicates. I also wrote a separate file to update where values differentiate between what I am receiving as (new) and what is in the database (old) which actually worked out great for my application.

Here is my answer for anyone else that runs into this issue :)

$prep_stmt = "SELECT * FROM table WHERE column_keys=?";
    $stmt = $mysqli->prepare($prep_stmt);

    if ($stmt) {
        $stmt->bind_param('s',$varvalues);
        $stmt->execute();
        $stmt->store_result();

        if ($stmt->num_rows == 1) {

        if ($insert_stmt = $mysqli->prepare("")) {
            $insert_stmt->bind_param('');

            if (! $insert_stmt->execute()) { 
                echo 'shits broke'; }
            }
        } 
        else { if ($insert_stmt = $mysqli->prepare("
                                            INSERT INTO table (column_keys)
                                            VALUES (?)")) // you will need a ? per column seperate by a , (?,?,?...?)

            { $insert_stmt->bind_param('s',
                                    $varvalues
                                    ); // you will also need to bind a 's' (string) 'i' for num, etc per $var value.

        if (! $insert_stmt->execute()) { echo 'shits broke';} //lol 
            }
    }
    }

Also a simple error reporting trick I stumbled upon that helped me clean up a few things I overlooked. Just place it at the top of the file, or above you want to debug ;)

error_reporting(E_ALL);

Upvotes: 0

jaggedsoft
jaggedsoft

Reputation: 4038

The following statement will be silently ignored if one of the fields with the flags UNIQUE or PRIMARY KEY already exist in the database. If you're searching for INSERT IF NOT EXISTS this is probably what you're looking for:

INSERT IGNORE INTO `members` SET name='Steve',start_date='2015-11-20';

You can also overwrite a record that already exists using REPLACE. If it doesn't yet exist, it will be created:

REPLACE INTO `members` SET name='Steve',start_date='2015-11-20';

Another thing to consider would be INSERT ... ON DUPLICATE KEY UPDATE syntax: 

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

Upvotes: 1

Andrius
Andrius

Reputation: 5939

You should try using INSERT ... ON DUPLICATE KEY UPDATE. Documentation

This does mean that you will have to define some unique (could be primary) key to the table that is always constant so MySQL knows what to update.

A quick example of how you would do it:

INSERT INTO table (f1,f2,f3) VALUES ('something_unique',2,5) 
ON DUPLICATE KEY UPDATE f2=2,f3=5

Upvotes: 1

Related Questions