Simple ON DUPLICATE KEY UPDATE?

Question

I'm trying to store a one-to-many relationship in my mySQL database and I want to avoid duplicate entries if at all possible.

I read on the internet and saw that 'ON DUPLICATE KEY UPDATE' is an option. Would this work for my situation?

CODE:

function insert_options($uid, $array) {
    if(!is_array($array)) {
        return false;
    }
    $db = db_connect();
    foreach($array as $a) {
        $sql = 'INSERT INTO newsletter_coupon_codes_options (uid, option_name, value) VALUES (?, ?, ?)';
        $stmt = $db->prepare($sql);
        if($stmt === false) {
            echo "Prepare failed: (" . $db->errno . ") " . $db->error;
        }
        $stmt->bind_param('iss', $uid, $a, $value);
        $value = '1';
        $stmt->execute();
    }
}

TABLE

UID(INT11), OPTION_NAME(varchar(255)), VALUE(INT(11))

I tried to add ON DUPLICATE KEY UPDATE value = 1 to the $sql statement, but it didn't seem to do anything. Basically I want to make sure when I'm inserting the data if the uid and option_name exist, it just sets the value to 1 and doesn't make a new entry.

I do not have a unique key set up, only a primary key on uid. I wasn't sure what to make unique. I can't make the uid unique, as there will be multiple entries per uid. I also cannot make the option_name unique as there will be the same option_name for multiple uid's

What is the best way to accomplish this?

Answer 1

Change your query to

INSERT INTO newsletter_coupon_codes_options (uid, option_name, value) 
VALUES (?, ?, ?)
ON DUPLICATE KEY UPDATE value = VALUES(value)

Here is SQLFiddle demo

And make sure that you have a UNIQIE or PRIMARY KEY constraint on (uid, option_name), e.g.

ALTER TABLE newsletter_coupon_codes_options 
  ADD UNIQUE (uid, option_name);

Answer 2

Please change your query like this and use mysqli_query function execute this kindof queries and use mysqli_real_escape_string function to avoid SQL injections

$sql = 'INSERT INTO newsletter_coupon_codes_options 
     (uid, option_name, value) VALUES ("'.$uid.'", "'.$option_name.'", "'.$value.'")
    ON DUPLICATE KEY UPDATE  value = "'.$value.'"';