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
– Daniel A. White, Commented Apr 2, 2013 at 1: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
– Revent, Commented Apr 2, 2013 at 1:14

Community · Accepted Answer · 2017-05-23 12:16:56Z

9

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?

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Apr 2, 2013 at 1:15

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2234155 Over a year ago

Works now!!! Thanks for the speedy answers and suggested reading. You guys are great :)

user2234155 Over a year ago

Hey, thanks for the heads up. I'm sure there will be more questions. Will remember next time ;)

Collectives™ on Stack Overflow

mysql creates a new row instead of updating existing

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related