问题描述
我不知道这是否正确.我有一个类，如果字段当前存在，我想更新数据库，如果不存在则插入.复杂之处在于我正在加入 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.layerFROM 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.layerFROM school_art在 baseimage.base_folder = school_art.series_code 上加入 baseimageWHERE baseimage.image_type = 'B' ORDER BY school_art.id");返回 $result_array;}相信你正在寻找以下语法:
INSERT INTO <table>(字段 1，字段 2，字段 3，...)值('value1'，'value2'，'value3'，...)重复密钥更新field1='value1', field2='value2', field3='value3', ...<块引用>
注意:使用ON DUPLICATE KEY UPDATE，如果行被插入<，则每行受影响的行值为1/em> 作为新行，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 is1if the row is inserted as a new row,2if an existing row is updated, and0if an existing row is set to its current values.
MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement
这篇关于PHP MYSQL UPDATE 如果存在或 INSERT 如果不存在?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！