SQL Prevent Duplicate INSERT [duplicate]

Question

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 $var values?

WHAT I WANT TO DO

IF new value is NOT old value use old value else use new value;

    $prep_stmt = "SELECT * FROM members WHERE record NOT LIKE record=? ";
$stmt = $mysqli->prepare($prep_stmt);

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

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

                                        VALUES (?)")) 

        {
        $insert_stmt->bind_param('s',$repStartDate);

    if (! $insert_stmt->execute()) {header('Location: ../error.php?err=Registration failure: INSERT');}
        }
}
}

Answer 1

If you don't want to update when the record exists then you can use Insert Ignore like below.

mysql> SELECT * FROM visit;
Empty set (0.00 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
|  1 |      32 | 1           |
+----+---------+-------------+
1 row in set (0.00 sec)

If you want to update when the record exists then you can use On Duplicate Key Update like below.

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
|  1 |      32 | 1           |
+----+---------+-------------+
1 row in set (0.00 sec)

mysql> INSERT IGNORE INTO visit (user_id, total_visit) VALUES (32, 1) ON DUPLICATE KEY UPDATE total_visit = total_visit + VALUES(total_visit);
Query OK, 2 rows affected (0.00 sec)

mysql> SELECT * FROM visit;
+----+---------+-------------+
| id | user_id | total_visit |
+----+---------+-------------+
|  1 |      32 | 2           |
+----+---------+-------------+
1 row in set (0.00 sec)

FULL EXAMPLE is here