Alisso Alisso - 7 months ago 9
PHP Question

on duplicate entry add name if empty php mysql

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
the 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

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);