将多个数据插入 MySQL 并更新(如果存在) [英] Insert multiple Data to MySQL and Update if existing

问题描述

我在同时插入和更新数据库时遇到问题，我有一个用户可以编辑信息或添加新字段的表单，我想要的是用户何时编辑表单以及用户是否添加了新字段(s) 我希望我可以一起插入和更新数据库.

I have a problem to insert and update database at the same time, I have a form that user can edit the information or add new field, what I want is when an user edits the form and if the user adds new filed(s) I want I can insert and update the database together.

这是我的代码

function insert_update_db(){
global $db;

$section = $_POST["page"];

$fieldsArray = array(   "section_id", // Primary key
                        "section_title",
                        "section_name",
                        "section_content",
                    );


$fields   = '`' . implode('`, `', $fieldsArray ) . '`';


$sql    = "INSERT INTO `db_section` ($fields) VALUES"; 
$valueArray = array();
$indexKey   = array(); 

foreach ($section["section"] as $value) {


    $section_id             = ($value["section_id"] != "" ? $db->quote($value["section_id"]) : "NULL" ); // Check if curr field has a ID
    $title                  = $value["title"];
    $name                   = $value["name"];
    $content                = $value["content"];


    $valueArray[] = "($section_id, '$title','$name', '$content')";

    if($section_id != "NULL"){
        $indexKey[] = str_replace("'", "", $section_id);
        $sql_update = "UPDATE `db_section` SET 
                                `section_title` = '$section_title',
                                `section_name` = '$name',
                                `section_content` = $content
                    WHERE `section_id` = $section_id;";

        $update = $db->query($sql_update);

        echo $sql_update;

        if($update){
            $db->sql_status = "Success";
        }
    }
}

$sql .= implode(",", $valueArray);

$sql .= " ON DUPLICATE KEY UPDATE ";

$sql .= "section_id=" . implode(" AND section_id=", $indexKey);

$insert = $db->query($sql);

if($insert){
    $db->sql_status = "Success";
}else{
    $db->sql_status = "Error";
}
}

在编辑页面中，我添加了隐藏输入 section_id 以获取正在编辑的字段的主键，并将为新字段提供 NULL，在我尝试执行的操作中do if section_id !='NULL' then UPDATE 字段.

In edit page I added hidden input section_id to get primary key for the field is being edit, and will give NULL for a new field, on action I try to do if section_id != 'NULL' then UPDATE the field.

我尝试使用 ON DUPLICATE KEY UPDATE 来检查 section_id 是否重复，但它根本不起作用.

I tried to use ON DUPLICATE KEY UPDATE to check if section_id is duplicate but it does not work at all.

对不起我的英语，任何帮助将不胜感激:)

Sorry about my English, any help will be appreciated :)

推荐答案

只需要一个索引冲突，该冲突将违反要更新的行的重复项，而不是要创建的新行.索引冲突可以是主键，也可以是另一个索引上的一个，可以是单列或跨多列的复合索引.

All it takes is one index clash that would violate a duplicate for the row to be updated, and not for a new row to be created. The index clash can be a primary key, a one on another index be it single column or composite index across multiple columns.

当然，下面的内容相当蹩脚，但我现在可以尽其所能.

Granted the below is rather lame, but as imaginative as I can do right now.

create table user
(
    id int auto_increment primary key,
    userName varchar(20) not null,
    friendCount int not null,
    unique key(userName)
);

insert user(userName,friendCount) values('Jason7',0) on duplicate key update friendCount=friendCount+1;
select * from user;
+----+----------+-------------+
| id | userName | friendCount |
+----+----------+-------------+
|  1 | Jason7   |           0 |
+----+----------+-------------+

insert user(userName,friendCount) values('Fred',0) on duplicate key update friendCount=friendCount+1;
select * from user;
+----+----------+-------------+
| id | userName | friendCount |
+----+----------+-------------+
|  1 | Jason7   |           0 |
|  2 | Fred     |           0 |
+----+----------+-------------+

insert user(userName,friendCount) values('Fred',0) on duplicate key update friendCount=friendCount+1;
+----+----------+-------------+
| id | userName | friendCount |
+----+----------+-------------+
|  1 | Jason7   |           0 |
|  2 | Fred     |           1 |
+----+----------+-------------+

这篇关于将多个数据插入 MySQL 并更新(如果存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！