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. https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — Sammitch, Oct 01 '15 at 22:01
id is primary, username is unique, but I'm using WHERE with session id ($user = $_SESSION['Username'];) — Anja, Oct 02 '15 at 15:35

score 1 · Answer 1 · answered Oct 01 '15 at 22:06

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 01 '15 at 22:06

Victor - Reinstate Monica

1,264
9
11

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.... – Anja Oct 02 '15 at 15:39
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. – Victor - Reinstate Monica Oct 03 '15 at 12:45

score 0 · Answer 2 · answered Oct 01 '15 at 22:05

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 01 '15 at 22:05

Rahul

76,197
13
71
125

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 Oct 02 '15 at 15:40

score 0 · Answer 3 · answered Oct 06 '15 at 15:50

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."'");            
            }
        }

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

3 Answers3