SQL: If exists, update else insert

Question

I am very new to SQL and PHP but I want to add a user to a list if an entry does not already exist with their ip. If the entry does exist I just want to update their username.

I can perform both of these operations outside of the "if exists" statement without hiccups but it obviously creates a lot of duplicates. When I wrap it in the "if exists" statement the query stops doing anything.

I know there are a few questions regarding the subject but I have tried following most of the advice, if there is another way to do this I would be happy to consider it, thank you.

<?php
$username = strval($_GET['username']);
$userip = $_SERVER['REMOTE_ADDR'];

$con = mysqli_connect("localhost", "ramendev_jctwood", "M1n1flam3", "ramendev_jctwood") or die ("Failed to connect to MySQL: " . mysqli_connect_error());

mysqli_query($con, "IF EXISTS (SELECT 1 FROM Users WHERE Userip='$userip')
                        UPDATE Users SET User='$username' WHERE Userip='$userip'
                    ELSE
                        INSERT INTO Users (User, Userip) VALUES ('$username', '$userip')
                            ");
?>

why not insert ... on duplicate key update? Any why are you not checking for sql errors? mysqli_query(...) or die(mysqli_error()) would tell you EXACTLY what's wrong. — Marc B
– Marc B, Commented Aug 29, 2014 at 16:19
Try adding a semi-colon at the end of the UPDATE line, and at the end of the INSERT line. — Tab Alleman
– Tab Alleman, Commented Aug 29, 2014 at 16:23
Many SQL DBMSs have extensions to handle that. I'm not sure which offers @Marc B's on duplicate key update, but SQLite has insert or replace into, and I'm sure there are other variations. — John Bollinger
– John Bollinger, Commented Aug 29, 2014 at 16:23
@john: it's mysql, which OP is obviously using since he's got mysqli_query() — Marc B
– Marc B, Commented Aug 29, 2014 at 16:24

Yanire Romero · Accepted Answer · 2014-08-29 16:23:42Z

2

You can try this:

IF (SELECT COUNT(*) FROM Users WHERE Userip='$userip') > 0
    UPDATE Users SET User='$username' WHERE Userip='$userip'
ELSE
    INSERT INTO Users (User, Userip) VALUES ('$username', '$userip')

answered Aug 29, 2014 at 16:23

Yanire Romero

4823 silver badges13 bronze badges

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

Comments

user3349436 · Accepted Answer · 2014-08-29 16:26:11Z

0

try this one

$query = "SELECT * FROM Users WHERE Userip='$userip'";
$check = mysql_query($query);
if(mysql_num_rows($check)) {
   $existdata = mysql_fetch_array($check);
   $newquery = "UPDATE Users SET User='$username' WHERE Userip='{$existdata['Userip']}'";
} else {
   $newquery = "INSERT INTO Users (User, Userip) VALUES ('$username', '$userip')";       
}
$execute = mysql_query($new_query);
if($execute){
   echo "Success";
} else {
   echo mysql_error();
}

answered Aug 29, 2014 at 16:26

user3349436

1515 bronze badges

Comments

Bulat · Accepted Answer · 2014-08-29 16:59:57Z

0

Here is what doc tells us for the case when you have primary key http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

edited Aug 29, 2014 at 16:59

answered Aug 29, 2014 at 16:40

Bulat

6,9992 gold badges32 silver badges54 bronze badges

Comments

Márcio Rossato · Accepted Answer · 2014-10-10 19:54:53Z

-1

in mysql you can use the REPLACE commmand.

REPLACE INTO users set User='$username' , Userip='$userip'

the field Userip mustbe unique index ou primary key

see more in http://dev.mysql.com/doc/refman/5.5/en/replace.html

edited Oct 10, 2014 at 19:54

answered Aug 29, 2014 at 16:23

Márcio Rossato

1,1491 gold badge13 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL: If exists, update else insert

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related