PHP MYSQL UPDATE if Exist or INSERT if not?

Question

I have no idea if this is even remotely correct. I have a class where I would like to update the database if the fields currently exist or insert if they do not. The complication is that I am doing a joining 3 tables (set_colors, school_art, baseimage)

Any help would be really great.

Here is what I have:

public function set_layer_colors($value) {
    global $db;

    $result_array = mysql_query("
    IF EXISTS(SELECT * FROM set_colors WHERE school_art_id = '{$value}')

      UPDATE set_colors (school_art_id, baseimage_id, sub_folder, layer)
        SELECT school_art.id, baseimage.id, baseimage.sub_folder, baseimage.layer
        FROM school_art 
        JOIN baseimage ON baseimage.base_folder = school_art.series_code 
        WHERE baseimage.image_type = 'B' ORDER BY school_art.id 

    ELSE

     INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
        SELECT school_art.id, baseimage.id, baseimage.sub_folder, baseimage.layer
        FROM school_art 
        JOIN baseimage ON baseimage.base_folder = school_art.series_code 
        WHERE baseimage.image_type = 'B' ORDER BY school_art.id 
        ");

    return $result_array;
}

score 196 · Accepted Answer · edited Dec 21 '20 at 12:05

196

I believe you are looking for the following syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement

edited Dec 21 '20 at 12:05

ashleedawg

20,365
9
72
105

answered Jul 28 '11 at 01:39

Tash Pemhiwa

7,590
4
45
49

6

To be more correct you don't need indication of Primary keys. So if field1 is a key then ...... ON DUPLICATE KEY UPDATE field2='value2', field3='value3', ... (starting with field2) – giacoder Jul 03 '14 at 08:38
1

just remember to give the target column the UNIQUE constraint – Robert Sinclair May 13 '18 at 00:43
1

@StefanoZanetti, your field names do not match: `psw` vs `password`. Further, you have not specified the `role` field in the ON DUPLICATE KEY UPDATE clause (I am not sure if this matters). – Tash Pemhiwa Apr 03 '19 at 10:55

score 34 · Answer 2 · edited Nov 07 '18 at 06:43

34

Two choices:

MySQL Manual :: INSERT INTO ... ON DUPLICATE KEY UPDATE Syntax

or

MySQL Manual :: REPLACE INTO Syntax

Both will allow you to do the insert-if-not-exists-or-update in one query.

edited Nov 07 '18 at 06:43

Community

1
1

answered Jul 28 '11 at 01:28

Dan Grossman

51,866
10
112
101

Thanks, REPLACE INTO is much better for my case otherwise I'd have to call "$query->send_long_data(1, $buff)" twice, one for the INSERT and one for the UPDATE – marco Jan 15 '15 at 14:05
6

Nooo... If we are using "REPLACE INTO ", the other FOREIGN KEY related table's data would be deleted if the PRIMARY data is exist. – Angga Lisdiyanto Apr 02 '16 at 08:04
1

Heads-up about `REPLACE` -- it implicitly deletes the entry it tries update and adds a new one, thus if you have an `AUTO_INCREMENT` key, such as `ID`, it will be auto-incremented too! – c00000fd Jan 03 '20 at 07:24

PHP MYSQL UPDATE if Exist or INSERT if not?

2 Answers2

Linked

Related