PHP MYSQL 更新(如果存在)或 INSERT(如果不存在)? [英] PHP MYSQL UPDATE if Exist or INSERT if not?

问题描述

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

任何帮助都会很棒.

这是我所拥有的:

公共函数 set_layer_colors($value) {全球 $db;$result_array = mysql_query("如果存在(SELECT * FROM set_colors WHERE school_art_id = '{$value}')更新 set_colors (school_art_id, baseimage_id, sub_folder, layer)选择 school_art.id、baseimage.id、baseimage.sub_folder、baseimage.layer来自 school_art在 baseimage.base_folder = school_art.series_code 上加入 baseimageWHERE baseimage.image_type = 'B' ORDER BY school_art.id别的INSERT INTO set_colors (school_art_id, baseimage_id, sub_folder, layer)选择 school_art.id、baseimage.id、baseimage.sub_folder、baseimage.layer来自 school_art在 baseimage.base_folder = school_art.series_code 上加入 baseimageWHERE baseimage.image_type = 'B' ORDER BY school_art.id");返回 $result_array;}

解决方案

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

INSERT INTO (field1, field2, field3, ...)值 ('value1', 'value2','value3', ...)关于重复密钥更新field1='value1', field2='value2', field3='value3', ...
<块引用>
注意:对于ON DUPLICATE KEY UPDATE，如果行被插入 作为新行，2 如果现有行被更新，0 如果现有行被设置为它的当前值.
MySQL 文档:INSERT ...ON DUPLICATE KEY UPDATE 语句
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;
}

解决方案
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
这篇关于PHP MYSQL 更新(如果存在)或 INSERT(如果不存在)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

                            
                        

            



        

            



        

    

        

            登录
            关闭
        
        

            

                扫码关注1秒登录
            
            

                
            
            

                
                
            
            

                发送“验证码”获取
                |
                15天全站免登陆
            
            

        
    

    

		

			友情链接：
            IT屋
            Chrome插件
            谷歌浏览器插件
        
        

            IT屋
            ©2016-2022 琼ICP备2021000895号-1
            站点地图
            站点标签
            SiteMap
            <免责申明>
            本站内容来源互联网,如果侵犯您的权益请联系我们删除.