mysql 创建一个新行而不是更新现有的 [英] mysql creates a new row instead of updating existing

问题描述

我正在尝试为特定用户更新 bio 列，但我得到了一个新行.经历过类似的问题，但无法弄清楚我的情况.

I'm trying to update a bio column for a specific user, but i get a new row instead. Been through similar questions but cant figure things out for my situation.

if (isset($_SESSION['logged_in']) && $_SESSION['name_of_user'] == $profile_name) {
    $pageOwner = true;
}
if (isset($_POST['submit'])) {
    $bio = $_POST['bio'];

    if (empty($bio)) {
        $error = "Missing Bio";
    } else {
        if ($member_bio->num_rows == 1) {

            if ($query = $db->query("UPDATE user_account SET 'bio'='$bio' WHERE 'id_user'='$id'")) {
                $ok_mesg = "Profile has been updated!!!";

            } else {
                $error = "ERROR on our end";
            }
        } else {
            if ($query = $db->query("INSERT INTO user_account (bio) VALUES('$bio')")) {

                $ok_msg = "Profile has been updated";
            } else {
                $error = "Error on our end";
            }
        }
    }
}

推荐答案

如果值已经存在，可以直接UPDATE，如果不存在，可以直接INSERT使用 INSERT...ON DUPLICATE KEY UPDATE

You can directly UPDATE if the value is already existing or INSERT if it doesn't exist by using INSERT...ON DUPLICATE KEY UPDATE

但首先你需要指定一个唯一的列，

But first you need to specify a unique column,

ALTER TABLE user_account ADD CONSTRAINT tb_uq UNIQUE (id_user)

如果 ID_USER 列已经是主键，则跳过第一种方法.实现后，您现在可以使用以下语法

if column ID_USER is already a primary key then skip the first method. After it has been implemented, you can now use the following syntax

INSERT INTO user_account (id_user, bio) 
VALUES($id, '$bio')
ON DUPLICATE KEY UPDATE bio = '$bio';

• INSERT ... ON DUPLICATE KEY UPDATE 语法

作为旁注，查询容易受到SQL注入的影响，如果变量的 value(s) 来自外部.请查看下面的文章，了解如何预防.通过使用 PreparedStatements，您可以摆脱在值周围使用单引号.

As a sidenote, the query is vulnerable with SQL Injection if the value(s) of the variables came from the outside. Please take a look at the article below to learn how to prevent from it. By using PreparedStatements you can get rid of using single quotes around values.

• 如何在 PHP 中防止 SQL 注入?

这篇关于mysql 创建一个新行而不是更新现有的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！