PHP MySQL - INSERT and ON DUPLIATE KEY UPDATE with WHERE?

Question

I want to be able for members to add more info (location, story) to their profile and also update their password if needed.

For that I use the following snippet:

$query = mysql_query("
    INSERT INTO 
        members (location, story) 
    VALUES 
        ('$location', '$story') 
    WHERE 
        username='$user' 
    ON DUPLICATE KEY UPDATE 
        hash = '$password', 
        location='$location', 
        story='$story' 
    ");

This does not work with the "WHERE" part, but if I remove it then the data just gets filled into an empty record, not the user record. How do I properly use the WHERE part in this snippet, so the correct user profile is updated?

I have searched up and down the internet and this website, but not found a single solution, which surprises me as this seems to be a very common question?

Does anyone know how to solve this problem?

Thanks in advance!

WHERE is invalid in this context. In order for ON DUPLICATE KEY to work the username field either needs to be the primary key, or a UNIQUE index. dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — Sammitch
– Sammitch, Commented Oct 1, 2015 at 22:01
id is primary, username is unique, but I'm using WHERE with session id ($user = $_SESSION['Username'];) — Anja
– Anja, Commented Oct 2, 2015 at 15:35

Victor - Reinstate Monica · Accepted Answer · 2015-10-01 22:06:38Z

1

First, mysql is deprecated, you should use mysqli.

Second, make sure you escape your values before entering them in queries. mysql_real_escape_string() is a bare minimum.

Third, INSERT / ON DUPLICATE KEY UPDATE does not accept WHERE clause. It's use is for avoid duplicating keys. For a simple user signup or whatever, you could avoid using IDs / auto increment and use username as primary key. But for this you'd need MyISAM or MySQL 5.6+ in order to have fulltext indexing, and in general, this is recommended.

But in this use case, your location and story would be always overwritten. If this is what you want, you can try whatever I've written in the previous paragraph.

answered Oct 1, 2015 at 22:06

Victor - Reinstate Monica

1,2789 silver badges11 bronze badges

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

2 Comments

Anja Over a year ago

I AM using "mysql_real_escape_string" on everything... that is not the issue... the issue is that it doesn't update the existing record but add a new record instead because I can't use WHERE....

Victor - Reinstate Monica Over a year ago

As I said, you won't be able to achieve what you want unless using username as PRIMARY KEY. Moreover, even then, you must check before whether password is supplied and if not, avoid inserting it in order not to 'reset' people's password un updating whatever you mean by story.

Rahul · Accepted Answer · 2015-10-01 22:05:47Z

0

You can use a INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE construct like

INSERT INTO 
    members (location, story) 
SELECT '$location', '$story'
FROM table_name
WHERE username='$user' 
ON DUPLICATE KEY UPDATE 
    hash = '$password', 
    location='$location', 
    story='$story'

answered Oct 1, 2015 at 22:05

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

Anja Over a year ago

Thanks Rahul, I was eager to try your solution, but unfortunately that doesn't work either.... I don't get an error, it just doesn't do anything to the database at all...

Anja · Accepted Answer · 2015-10-06 15:50:52Z

Ok, I was finally able to solve this problem on my own. For anyone who is interested, here is the code:

// if user entered location         
        if (!empty($_POST['location']))
        {
            // if database row empty
            if ($row['location'] == " ")
            {
                mysql_query("INSERT INTO members (location) VALUES ('".$location."') WHERE username='".$user."'");  
            }
            // if database row not empty
            else 
            {
                mysql_query("UPDATE members SET location='".$location."' WHERE username='".$user."'");      
            }
        }
        // if user entered story
        if (!empty($_POST['story']))
        {
            // if database row empty
            if ($row['story'] == " ")
            {
                mysql_query("INSERT INTO members (story) VALUES ('".$story."') WHERE username='".$user."'");    
            }
            // if database row not empty
            else 
            {
                mysql_query("UPDATE members SET story='".$story."' WHERE username='".$user."'");            
            }
        }

Collectives™ on Stack Overflow

PHP MySQL - INSERT and ON DUPLIATE KEY UPDATE with WHERE?

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related