on duplicate entry add name if empty php mysql

Question

Clarification of what I'm trying to accomplish: Update "user name", if there is a record with the same e-mail but no name. if user already has a name, then don't make any changes

I have a website where all users can be "subscribers, entrepreneurs or investors". If a subscriber (of whom I have previously only asked for email) chooses to upload a business idea then that person will probably use the same e-mail address as before only this time adding a name. So I'd like to INSERT INTO, and if e-mail already exists - add the name, but only if there is not a name there already (so that a person cannot simply over write somebody else's details).

I've gotten this far:

 mysql_query("
     INSERT INTO users
         (email, name)
     VALUES
         ('" .$epost. "',  '" .$namn. "')
    ON DUPLICATE KEY UPDATE
    name=VALUES(name)   -->[if name == '', else do nothing...]
 ");

Now it replaces the current name with a new one if different.

I searched "on duplicate update if field empty" and found: http://forums.aspfree.com/sql-development-6/on-duplicate-key-update-but-only-if-value-isn-t-482012.html (merging?)

conditional on duplicate key update (closer, but I don't want to update it if it differs, only if the field is empty.)

http://bytes.com/topic/php/answers/914328-duplicate-key-update-only-null-values (if the input is blank.. well it's not)

http://boardreader.com/thread/Insert_on_duplicate_key_update_only_if_c_can8Xachr.html (the layout of this page got me lost)

http://www.digimantra.com/tutorials/insert-update-single-mysql-query-duplicate-key/ (it simply updates)

I believe this might be kind of what I'm after (?) http://thewebfellas.com/blog/2010/1/18/conditional-duplicate-key-updates-with-mysql but I haven't managed to make it work

This code:

 mysql_query("UPDATE users SET name='".$namn."'
WHERE email='".$epost."' AND name =''");

updates the name, only if it's previously null, and that's what I'm after however it does not insert a new record if email doesn't already exist.

My table

So I tried this:

  mysql_query("
     INSERT INTO users
     SELECT email, 'victoria' FROM users 
     WHERE email='victoria@hejsan.se' ON DUPLICATE KEY UPDATE name = 'victoria'
 ");

and I tried this:

   mysql_query("
    INSERT INTO users
    SELECT email, 'yay' from users
    WHERE email='victoria@hejsan.se'
    ON DUPLICATE KEY
    UPDATE name = values(name)
 ");

from @Fluffeh 's answer

but nothing happens. Did i misinterpret the answer?

It just hit me that if someone already has an account then they should also have a password already, and so I'll just ask them to verify their password, and make sure I do an AJAX call while they insert their e-mail to see if the e-mail is already registered :) And so then this is no longer an issue, and I think that is the solution I'll go with. !)

Answer 1

The only way that you could use the insert .... on duplicate key... syntax to solve your issue would be if name and email made a composite key - and I think you would be better off using an auto_increment as a primary key.

You might have to put a little logic into the PHP to do a check first, then insert or update - or write a function to do that same test for you - but neither will be a simply query that you can just fire off.

Edit: Not sure if this is what you want to do, but I think that the best solution for your requirements is to actually use two tables in a one to many relationship.

Table-Users
id | email | name

create table users(
    id int(10) not null auto_increment,
    email varchar(100),
    name varchar(100),
    primary key(email, name)
    );

Table-Ideas
id | userID | idea

create table users(
    id int(10) not null auto_increment primary key,
    userID int(10) not null,
    idea text
    );

With the primary key on the table, you can safetly do an insert... duplicate... without worrying about over-writing folks. The second table will however allow you to have the ideas stored safetly locked to the user, and let you have a number of ideas per user. As the relationship is the users.id to ideas.userID you won't lose who owns it even if their details are updated.

Edit: (aka, ZOMG facepalm)

$query="
    update users 
        set name='".$userName."' 
    where 
        email='".$userEmail."' 
        and name is null";

Edit 2: (aka, wipes brow)

insert into users 
    select email, '".$namn."' from users where email='".$epost."' 
    on duplicate key 
        update name = values (name);

and here it is working:

mysql> create table test1 (myName varchar(10) unique, myEmail varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into test1 values ('Tom','something');
Query OK, 1 row affected (0.01 sec)

mysql> insert into test1 values('Nick',null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from test1;
+--------+-----------+
| myName | myEmail   |
+--------+-----------+
| Tom    | something |
| Nick   | NULL      |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> insert into test1 select myName, myEmail from test1 
where myName='Tom' on duplicate key update myEmail = values (myEmail);
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+--------+-----------+
| myName | myEmail   |
+--------+-----------+
| Tom    | something |
| Nick   | NULL      |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> insert into test1 select 'Tom', myEmail from test1 
where myName='Tom' on duplicate key update myEmail = values (myEmail);
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+--------+-----------+
| myName | myEmail   |
+--------+-----------+
| Tom    | something |
| Nick   | NULL      |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> insert into test1 select myName, 'Something Else' from test1 
where myName='Tom' on duplicate key update myEmail = values (myEmail);
Query OK, 2 rows affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 1  Warnings: 1

mysql> select * from test1;
+--------+------------+
| myName | myEmail    |
+--------+------------+
| Tom    | Something  |
| Nick   | NULL       |
+--------+------------+
2 rows in set (0.00 sec)

mysql> insert into test1 select myName, null from test1 
where myName='Nick' on duplicate key update myEmail = values (myEmail);
Query OK, 0 rows affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from test1;
+--------+------------+
| myName | myEmail    |
+--------+------------+
| Tom    | Something  |
| Nick   | NULL       |
+--------+------------+
2 rows in set (0.00 sec)

mysql> insert into test1 select myName, 'yay' from test1 
where myName='Nick' on duplicate key update myEmail = values (myEmail);
Query OK, 2 rows affected (0.01 sec)
Records: 1  Duplicates: 1  Warnings: 0

mysql> select * from test1;
+--------+------------+
| myName | myEmail    |
+--------+------------+
| Tom    | Something  |
| Nick   | yay        |
+--------+------------+
2 rows in set (0.00 sec)

Edit 3: Try this for your $query

insert into table1 select coalesce(email,'".$epost."') as email, coalesce(name,'".$namn."') as name from table1 
where email='".$epost."' on duplicate key update name = values (name);

Answer 2

If you want to update a field that is already exist, you can do this as;

-- MySQL Reference
INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

-- so your code
INSERT INTO users
         (email, name)
     VALUES
         ('" .$epost. "',  '" .$namn. "')

     ON DUPLICATE KEY UPDATE email=VALUES(email), name=VALUES(name)

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

UPDATE

At this point, you need "MySQL conditional insert". Let's try it;

CREATE TABLE IF NOT EXISTS `users` (
  `name` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  UNIQUE KEY `email` (`email`)
)

INSERT INTO users (name, email) VALUES('kerem', 'qeremy[atta]gmail[dotta]com');
INSERT INTO users (name, email) VALUES('test', 'test@example.com');

>> 2 rows inserted. ( Query took 0.0005 sec )

-- this is your part (regarding email field is UNIQUE, so WHERE email='search email')

INSERT INTO users (name, email)
    SELECT 'test', 'test@example.com' FROM DUAL WHERE NOT EXISTS (
        SELECT * FROM users WHERE email = 'test@example.com' LIMIT 1
    );

>> 0 rows inserted. ( Query took 0.0003 sec )

https://www.google.com/search?q=conditional+insert+mysql

http://allurcode.com/2011/04/15/mysql-conditional-insert/ (which is useful search result)

UPDATE 2

If this does not work for you, then you can do your work like (just an input);

mysql_query("INSERT IGNORE INTO users (name, email) VALUES('$name', '$email')");
if (!mysql_insert_id()) {
   mysql_query("UPDATE users SET name='$name' WHERE name='' AND email='$email'");
}

Or;

mysql_query("UPDATE users SET name='$name' WHERE name='' AND email='$email'");
if (mysql_affected_rows() < 1) {
   mysql_query("INSERT INTO users (name, email) VALUES('$name', '$email')");
}

Answer 3

I'd like to suggest this solution using the control function IFNULL(exp1,exp2), defined as "if exp1 is not null, returns exp1; if exp1 is null, returns exp2".

http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html

INSERT INTO users (name, email) VALUES ('$name','$email')
ON DUPLICATE KEY UPDATE name = IFNULL(name, VALUES(name))

Of course you must be beyond this somehow, but it helped me.