php and mysqli SELECT query if it exists, then update it, else insert it

Question

I am running a query to check my database for existing values, if they do exist then update them do not reinsert them.

Currently my code is just reinserting the values into the table instead of updating them, which means my code is never reaching the else part of my statement.

if(isset($user_info->error)){
    // Something's wrong, go back
    header('Location: twitter_login.php');
} 
else {
   // Let's find the user by its ID
   $query = ("SELECT * FROM twitter WHERE oauth_provider = 'twitter' && oauth_uid = '".$user_info->id."'");
   $rs=$mysql->query($query);
   $results = $rs->fetch_array(MYSQLI_ASSOC);
   // If not, let's add it to the database
   if(empty($result)){
      $query = ("INSERT INTO twitter (oauth_provider, oauth_uid, username, oauth_token, oauth_secret)
                 VALUES ('twitter', {$user_info->id}, '{$user_info->screen_name}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}')");
      $rs=$mysql->query($query);
      $insert = ("SELECT * FROM twitter WHERE id = " . mysqli_insert_id($mysql));
      $rs=$mysql->query($insert);
      $results = $rs->fetch_array(MYSQLI_ASSOC);
   } 
else{
    // Update the tokens
    $update = ("UPDATE twitter SET oauth_token = '{$access_token['oauth_token']}',
                oauth_secret = '{$access_token['oauth_token_secret']}'
                WHERE oauth_provider = 'twitter' AND oauth_uid = {$user_info->id}");
    $rs=$mysql->query($update);
    $rs->close();
}

It is reinserting the values into the database = duplicate rows... I don't want that. — AntonB, Jul 25 '14 at 14:12
`$results` or `$result` ? Cause you check for `empty($result)` — Raphaël Althaus, Jul 25 '14 at 14:12
Try this answer: http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql — wogsland, Jul 25 '14 at 14:15

score 1 · Accepted Answer · answered Jul 25 '14 at 14:13

you have a typo between $result and $results

$results = $rs->fetch_array(MYSQLI_ASSOC);
    // If not, let's add it to the database
    if(empty($result)){
                    ^
                   Here

as $result is not declared before it will always be empty and the if part will always be true.

score 1 · Answer 2 · answered Jul 25 '14 at 14:45

1

If you want to properly prevent duplicates, create a UNIQUE KEY(oauth_provider,oauth_uid);

then use INSERT ON DUPLICATE statement to insert or update the row. http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

INSERT INTO twitter (oauth_provider, oauth_uid, username, oauth_token, oauth_secret)
VALUES ('twitter', {$user_info->id}, '{$user_info->screen_name}', '{$access_token['oauth_token']}', '{$access_token['oauth_token_secret']}') 
ON DUPLICATE KEY UPDATE oauth_token = VALUES(oauth_token), oauth_secret = VALUES(oauth_secret)

answered Jul 25 '14 at 14:45

Naktibalda

12,083
5
31
46

would this eliminate my last `else { update }` statement? @naktibalda – AntonB Jul 25 '14 at 19:57
yes, it replaces SELECT, INSERT and UPDATE in your code. – Naktibalda Jul 26 '14 at 10:03

php and mysqli SELECT query if it exists, then update it, else insert it

2 Answers2