How to Update or Insert MySQL Records Using Conditional Statements in PHP?

PHP and MySQL: Updating or Inserting Records Using Conditional Statements

In your PHP application, you need a versatile way to update or insert data into your MySQL database based on whether a certain condition is met. This can be achieved using a combined update or insert statement.

The following code snippet demonstrates this approach:

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

    $query = "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 
    ON DUPLICATE KEY UPDATE
        school_art_id=VALUES(school_art_id),
        baseimage_id=VALUES(baseimage_id),
        sub_folder=VALUES(sub_folder),
        layer=VALUES(layer);";

    $result_array = mysql_query($query);

    return $result_array;
}

This code combines an INSERT and an ON DUPLICATE KEY UPDATE statement. The INSERT statement attempts to insert a new row into the set_colors table, with the values being retrieved from the join of the school_art and baseimage tables.

If the specified school_art_id already exists in the set_colors table, the ON DUPLICATE KEY UPDATE clause becomes active. This clause updates the existing row with the values provided in the VALUES clause, essentially overwriting the existing values. If the school_art_id does not exist, the new row is inserted as usual.

The above is the detailed content of How to Update or Insert MySQL Records Using Conditional Statements in PHP?. For more information, please follow other related articles on the PHP Chinese website!