Checking for Unique Key and updating row or creating a new one WordPress

Question

I have a draggable div in which the position is being saved to database with user_id set to unique. How would I check if user_id exists.. and if it does, update the other 3 columns.. If it does not exist, insert new row. I have read to use "ON DUPLICATE KEY UPDATE", but having a hard time implementing it. Any thoughts?

As explained by @N.B. - Working solution

global $wpdb;

    $_POST['user_id'];
    $_POST['div_id'];
    $_POST['x_pos'];
    $_POST['y_pos'];

    $user_id    = $_POST['user_id'];
    $div_id     = $_POST['div_id'];
    $x_pos      = $_POST['x_pos'];
    $y_pos      = $_POST['y_pos'];

$wpdb->query($wpdb->prepare(" INSERT INTO coords

   (user_id, div_id, x_pos, y_pos)
    VALUES (%d, %s, %d, %d)
    ON DUPLICATE KEY UPDATE
    x_pos = VALUES(x_pos), y_pos = VALUES(y_pos)",

    $user_id,
    $div_id,
    $x_pos,
    $y_pos
    ));

Answer 1

As @N.B. pointed out in the comments, while the first method I submitted works, it is open to race conditions. I'd like to thank him for pointing that out. Here is that first solution with race conditions:

$user_exists = $wpdb->get_var(
    $wpdb->prepare("SELECT user_id FROM coords WHERE user_id = %d", $user_id)
);
if($user_exists) {
    // Do Update
}
else {
    // Do Insert
}

These race conditions are astronomical, as an insert must finish executing in the time between the first query returning and the next insert query. But the race condition exists none-the-less, and therefore could happen at some point in time.

However your database is still safe. When it occurs it won't duplicate the data, but rather it will throw a wpdb error that a unique key already exists and the insert will silently fail (it won't terminate the script and it won't output the error unless error reporting is turned on).

WordPress database error: [Duplicate entry '3' for key 'PRIMARY']

Amazingly, the above technique is used in the Wordpress core and by countless plugin and theme authors, and I could only find two instances of 'ON DUPLICATE' being used correctly in the Wordpress core. So a large chunk of the internet runs with multiple instances of this race condition seemingly just fine, just to give you an idea of the astronomical chance we're talking about.

Regardless of the chance, to use it is bad practice. As N.B. commented, the database should worry about the data and not PHP.

The Real Solution

Wordpress, for whatever reason, does not have an 'INSERT ON DUPLICATE UPDATE' function, which means you have to either write up a query each time with $wpdb->query or build your own function to handle it. I went with writing a function because writing wpdb->query each time is a pain and brings the user one layer closer to accidental mysql injection. Also development speed.

/**
 * Insert on Duplicate Key Update.
 *
 * Wordpress does not have an 'insert on duplicate key update' function, which
 * forces user's to create their own or write standard queries. As writing
 * queries is annoying and open to mysql injection via human error, this function
 * automates this custom query in an indentical fashion to the core wpdb functions.
 * Source: http://stackoverflow.com/a/31150317/4248167
 *
 * @global wpdb $wpdb
 * @param string $table The table you wish to update.
 * @param array $data The row you wish to update or insert, using a field => value associative array.
 * @param array $where The unique keys that you want to update at or have inserted, also a field => value array.
 * @param array $data_formats Wordpress formatting array for the data. Will default to %s for every field.
 * @param array $where_formats Wordpress formatting array for the where. Will default to %s for every field.
 * @return boolean True if successfully inserted or updated the row.
 */
function insertOrUpdate($table, $data, $where, $data_formats = array(), $where_formats = array())
{
    if(!empty($data) && !empty($where)) {
        global $wpdb;
        // Data Formats - making sure they match up with the data.
        $default_data_format = (isset($data_formats[0])) ? $data_formats[0] : '%s';
        $data_formats = array_pad($data_formats, count($data), $default_data_format);
        $data_formats = array_splice($data_formats, 0, count($data));
        // Where Formats - making sure they match up with the where data.
        $default_where_format = (isset($where_formats[0])) ? $where_formats[0] : '%s';
        $where_formats = array_pad($where_formats, count($where), $default_where_format);
        $where_formats = array_splice($where_formats, 0, count($where));
        // Get Fields
        $data_fields = array_keys($data);
        $where_fields = array_keys($where);
        // Create Query
        $query =
            "INSERT INTO $table" .
            " (" . implode(', ', array_merge($data_fields, $where_fields)) . ")" .
            " VALUES(" . implode(', ', array_merge($data_formats, $where_formats)) . ")" .
            " ON DUPLICATE KEY UPDATE";
        // Compile update fields and add to query
        $field_strings = array();
        foreach($data_fields as $index => $data_field) {
            $field_strings[] = " $data_field = " . $data_formats[$index];
        }
        $query .= implode(', ', $field_strings);
        // Put it all together - returns true on successful update or insert 
        // and false on failure or if the row already matches the data.
        return !!$wpdb->query(
            $wpdb->prepare(
                $query,
                array_merge(
                    array_merge(
                        array_values($data),
                        array_values($where)
                    ),
                    array_values($data)
                )
            )
        );
    }
    return false;
}

To use it, you simply enter parameters just like you would with a $wpdb->update function call.

insertOrUpdate(
    'testing_table',
    array('column_a' => 'hello', 'column_b' => 'world'),
    array('id' => 3),
    array('%s', '%s'),
    array('%d')
);

In your case, it would be:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id),
    array('%d', '%d', '%d'),
    array('%d')
);

Or you can just use a default formatting:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id),
    array('%d')
);

Or you can ignore the formatting which will default to formatting as strings:

insertOrUpdate(
    'coords', 
    array('div_id' => $div_id, 'x_pos' => $x_pos, 'y_pos' => $y_pos), 
    array('user_id' => $user_id)
);

If you find any issues just let me know.