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, Aug 29 '14 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, Aug 29 '14 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, Aug 29 '14 at 16:23
@john: it's mysql, which OP is obviously using since he's got `mysqli_query()` — Marc B, Aug 29 '14 at 16:24
possible duplicate of [SQL standard UPSERT call](http://stackoverflow.com/questions/15252213/sql-standard-upsert-call) — Bulat, Aug 29 '14 at 16:38

score 2 · Answer 1 · answered Aug 29 '14 at 16:23

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 '14 at 16:23

Yanire Romero

480
3
13

score 0 · Answer 2 · answered Aug 29 '14 at 16:26

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();
}

Bulat · Answer 3 · 2014-08-29T16:59:57.297

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 '14 at 16:59

answered Aug 29 '14 at 16:40

Bulat

6,869
1
29
52

Márcio Rossato · Accepted Answer · 2014-10-10T19:54:53.083

-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 '14 at 19:54

answered Aug 29 '14 at 16:23

Márcio Rossato

971
1
11
20

SQL: If exists, update else insert

4 Answers4