Run an INSERT or an UPDATE

[lurtnowski]

I’m using php/mysql and am trying to run an INSERT query or an UPDATE query.
I want to run an INSERT if it is a new record, and an UPDATE if its not a new record.
So im guessing the code is something like

  $ac_unit_id = 7;
  $x_coord = 3;
  $y_coord = 2;
  $room_id = 1;

  //Im guessing if the FK ($ac_unit_id ) exists in the object_location table we only run an update, but if the FK is not set, dont we need to create it?
if() {
$sql = "INSERT INTO object_locations (type,room_id,ac_unit_id,x_coord,y_coord) VALUES ('ac_unit',".$room_id.",".$ac_unit_id.",".$x_coord.",".$y_coord.")";
} else {
$sql = "UPDATE object_locations SET room_id = ".$room_id..", ".$x_coord = ".$x_coord.", y_coord = ".$y_coord." WHERE ac_unit_id = ".$ac_unit_id.";
}

[DarthGuido]

https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

[m_hutley]

1. “Use prepared statements” (required response)
2. You’ve got a few typos in your update query.
3. As Guido mentioned, ON DUPLICATE KEY UPDATE was made for this.

[lurtnowski]

I was kind of intimidated to jumping into PDO, but it really seems not to be bad.
I was trying one of those ON DUPLICATE KEY UPDATE queries and this happened.

But if I look at the table, the furniture_id of 1 allready exists

then, when I look at the structure of the table, I notice that furniture_id is a UNIQUE KEY

So why dddddddddddid the query create a new row and npt update the on there?
Heres how I ma de the table

create table object_locations (
   object_location_id INT NOT NULL AUTO_INCREMENT,
   title VARCHAR(10),
   rack_id INT,
   furniture_id TINYINT,
   ac_unit_id TINYINT,
   type ENUM ('rack','ac_unit','power_panel','bay','row','furniture'),
   row TINYINT,
   room_id TINYINT,
   x_coord INT,
   y_coord INT,
   width SMALLINT,
   height SMALLINT,
   FOREIGN  KEY ( furniture_id ) REFERENCES furnishings ( furniture_id ),
   FOREIGN  KEY ( ac_unit_id ) REFERENCES ac_units ( ac_unit_id ),
   FOREIGN  KEY ( rack_id ) REFERENCES racks( rack_id ),
   UNIQUE KEY (rack_id, furniture_id, ac_unit_id ),
   PRIMARY KEY ( object_location_id )
);

[m_hutley]

@lurtnowski

UNIQUE KEY (rack_id, furniture_id, ac_unit_id ),

This does not make furniture_id a unique key, to be clear.

It makes the TUPLE of rack_id,furniture_id, and ac_unit_id unique.

What does row 538 look like, for the record?