如何根據存在性更新或插入MySQL中的記錄？

PHP MySQL: Updating or Inserting Records Based on Existence

In MySQL, you may encounter the need to update existing records or insert new ones based on whether certain fields already exist in the database. This task can be efficiently handled using a combination of the IF EXISTS and ELSE statements.

To update records if they exist or insert them if they do not, consider the following syntax:

IF EXISTS(SELECT * FROM table_name WHERE field = 'value')
  UPDATE table_name SET field1 = 'value1', field2 = 'value2', ...
ELSE
  INSERT INTO table_name (field1, field2, ...) VALUES ('value1', 'value2', ...)

In your specific scenario, to update or insert records in the 'set_colors' table based on the existence of corresponding records in the 'school_art' and 'baseimage' tables, you can modify your code as follows:

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 SET school_art_id = '{$value}', baseimage_id = '{$baseimage_id}', sub_folder = '{$sub_folder}', layer = '{$layer}'
    ELSE
      INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)
      VALUES ('{$value}', '{$baseimage_id}', '{$sub_folder}', '{$layer}')
    ");

    return $result_array;
}

This code checks for the existence of a record in the 'set_colors' table where the 'school_art_id' matches the provided value. If the record exists, it updates the specified fields. Otherwise, it inserts a new record into the table.

以上是如何根據存在性更新或插入MySQL中的記錄？的詳細內容。更多資訊請關注PHP中文網其他相關文章！