PHP MYSQL 如果存在则更新，如果不存在则插入？

发布于 2024-11-27 02:17:05 字数 1023 浏览 3 评论 0原文

我不知道这是否正确。我有一个类，如果字段当前存在，我想更新数据库，如果不存在则插入。复杂的是我正在做一个连接 3 个表（set_colors、school_art、baseimage）

任何帮助都会非常好。

这是我所拥有的：

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;
}

原文

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;
}

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羞稚 2024-12-04 02:17:05

我相信您正在寻找以下语法：

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

注意：使用ON DUPLICATE KEY UPDATE时，如果插入了行，每行的affect-rows值为1 /em> 作为新行，2 如果现有行已更新，0 如果现有行设置为其当前值。

MySQL 文档： INSERT ... ON DUPLICATE KEY更新语句

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

回复收藏 0 原文