php - 为每个数组运行插入重复更新

Question

这是我通过 POST 获得的数组，

Array
(
    [event_id] => 1
    [fights] => Array
        (
            [0] => Array
                (
                    [fight_id] => 1
                    [fighter_a_id] => 1
                    [fighter_b_id] => 2
                    [winner_id] => 
                )

            [1] => Array
                (
                    [fight_id] => 2
                    [fighter_a_id] => 4
                    [fighter_b_id] => 3
                    [winner_id] => 3
                )

        )

)

这是我现在正在尝试的 PHP 代码，但它返回了我没有得到的 23000 PDO 错误。我读到这是重复输入错误，但 ON DUPLICATE KEY UPDATE 不应发生这种情况。我认为 foreach 可能是导致错误的原因？有什么建议吗？

if (isset($_POST['fights'])){
    $event_id = $_POST['event_id'];
    foreach ($_POST['fights'] as $field){
        if ($field['fight_id'] != '') $fight_id = $field['fight_id']; else $fight_id = '';
        if ($field['fighter_a_id'] != '') $fighter_a_id = $field['fighter_a_id']; else $fighter_a_id = '';
        if ($field['fighter_b_id'] != '') $fighter_b_id = $field['fighter_b_id']; else $fighter_b_id = '';
        if ($field['winner_id'] != '') $winner_id = $field['winner_id']; else $winner_id = '';
        $set_fights = $DBH->prepare('
        INSERT INTO 
            fights (
                fight_id, 
                fighter_a, 
                fighter_b, 
                winner, 
                event           
                )
        VALUES (
            :fight_id,
            :fighter_a, 
            :fighter_b, 
            :winner, 
            :event
        )
        ON DUPLICATE KEY UPDATE
            fighter_a = :fighter_a, 
            fighter_b = :fighter_b, 
            winner = :winner, 
            event = :event
        ');
        $set_fights->bindParam(':fight_id', $fight_id, PDO::PARAM_INT);
        $set_fights->bindParam(':fighter_a', $fighter_a, PDO::PARAM_INT);
        $set_fights->bindParam(':fighter_b', $fighter_b, PDO::PARAM_INT);
        $set_fights->bindParam(':winner', $winner, PDO::PARAM_INT);
        $set_fights->bindParam(':event', $event_id, PDO::PARAM_INT);
        $set_fights->execute();
    }
}
try {

}
catch (PDOException $e) {
   echo $e->getMessage();
}  

Answer 1

我认为您需要像这样使用 LAST_INSERT_ID:

INSERT INTO fights (fight_id, fighter_a, fighter_b, winner, event)
VALUES (:fight_id, :fighter_a, :fighter_b, :winner, :event)
ON DUPLICATE KEY UPDATE
    fight_id = LAST_INSERT_ID(fight_id),
    fighter_a = :fighter_a, 
    fighter_b = :fighter_b, 
    winner = :winner, 
    event = :event