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
– AntonB, Commented Jul 25, 2014 at 14:12
$results or $result ? Cause you check for empty($result) — Raphaël Althaus
– Raphaël Althaus, Commented Jul 25, 2014 at 14:12

Fallen · Accepted Answer · 2014-07-25 14:13:25Z

1

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.

answered Jul 25, 2014 at 14:13

Fallen

4,5833 gold badges30 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Naktibalda · Accepted Answer · 2014-07-25 14:45:25Z

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, 2014 at 14:45

Naktibalda

14.2k5 gold badges37 silver badges53 bronze badges

2 Comments

AntonB Over a year ago

would this eliminate my last else { update } statement? @naktibalda

Naktibalda Over a year ago

yes, it replaces SELECT, INSERT and UPDATE in your code.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related