mysql creates a new row instead of updating existing

Question

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

**warning** your code may be vulnerable to sql injection attacks! — Daniel A. White, Apr 02 '13 at 01:11
Your first query is probably failing because no row exists with that user id, so the logic then falls into your else statement. Check your field names. Then validate the $id value prior to running the query. — Revent, Apr 02 '13 at 01:14

score 9 · Accepted Answer · edited May 23 '17 at 12:16

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?

Works now!!! Thanks for the speedy answers and suggested reading. You guys are great :) — user2234155, Apr 02 '13 at 01:33
Hey, thanks for the heads up. I'm sure there will be more questions. Will remember next time ;) — user2234155, Apr 02 '13 at 02:00

mysql creates a new row instead of updating existing

1 Answers1

Linked