UPDATE if exists else insert

Question

What is the correct format for UPDATE if exists else insert in this situation - I have managed to do update and insert separately but need them together?.

What I want is that - if the date exists then it gets updated to fullday, and if it doesn't exist then a new row with the new details gets inserted.

$date = (isset($_POST['date']) ? $_POST['date'] : null);
$reservationType = (isset($_POST['reservation-type']) ? $_POST['reservation-type'] : null);

$connect = new mysqli($servername, $username, $password, $dbname);


// check connection 

if ($connect->connect_error) {
    die("connection failed: ". $connect->connect_error);
}

$sql = "SELECT * FROM reservations";
$result = $connect->query($sql);

if ($result -> num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        if ($date == $row['date_of_reservation']) {
           "UPDATE reservations SET reservationType = 'FULLDAY' WHERE   date_of_reservation='$date'";
    }
    else {
        "INSERT INTO reservations (date_of_reservation, reservationType) VALUES ('$date', '$reservationType')";
        }
    }
}

[`INSERT ... ON DUPLICATE KEY UPDATE`](https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) -> `If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.` — Sean, Apr 25 '15 at 13:41
the problem is that the given code doesnot work - as in nothing happens to the database — ChriMarie, Apr 25 '15 at 13:50
You are not doing a query, you just defined 2 query strings. You need to do a `$connect->query()` on those strings. — Sean, Apr 25 '15 at 14:03

score 5 · Accepted Answer · answered Apr 25 '15 at 13:42

5

From the manual:

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

answered Apr 25 '15 at 13:42

rjdown

9,162
3
32
45

Can you explain what is the meaning of c=c+1? – ChriMarie Apr 25 '15 at 13:47
3

*♫ [c is for cookies, that's good enough for me](http://www.youtube.com/watch?v=BovQyphS8kA) ♫* - Seriously, it stands for "column". @ChriMarie – Funk Forty Niner Apr 25 '15 at 13:48
and what does the +1 at the end mean? – ChriMarie Apr 25 '15 at 15:14
The +1 us just adding 1 to the current value. Instead you probably want `ON DUPLICATE KEY UPDATE reservationType = 'FULLDAY'` – rjdown Apr 25 '15 at 17:36
Apologies for the late reply but had a super busy weekend. Your last suggestion - in the comment works perfectly :) – ChriMarie Apr 27 '15 at 00:12

UPDATE if exists else insert

1 Answers1