mysql creates a new row instead of updating existi

问题:

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";
            }
        }
    }
}

回答1:

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)

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 Syntax

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.

• How to prevent SQL injection in PHP?