Geek asks and answers

When re-writing add name if php mysql is empty

Explanation of what I am trying to do: Update "username" if there is an entry with the same email address but not the name. if the user already has a name, then do not make any changes

I have a website where all users can be "subscribers, entrepreneurs or investors." If the subscriber (whom I previously requested only by email) decided to upload a business idea, then this person will probably use the same email address as before, only this time by adding a name. Therefore, I would like to enter INSERT INTO, and if the email already exists, add a name, but only if there is no name (so that a person cannot simply rewrite some details).

I got 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 it is different.

I searched "when updating again if the field is blank" and found: http://forums.aspfree.com/sql-development-6/on-duplicate-key-update-but-only-if-value-isn-t- 482012.html (merge?)

(, , , .)

http://bytes.com/topic/php/answers/914328-duplicate-key-update-only-null-values ( .. )

http://boardreader.com/thread/Insert_on_duplicate_key_update_only_if_c_can8Xachr.html ( )

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

, (?) http://thewebfellas.com/blog/2010/1/18/conditional-duplicate-key-updates-with-mysql, .

:

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

, , , , .

the table

, :

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

:

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

@Fluffeh

. ?

, - , , , AJAX, e- mail, , :) , , , .!)

+5
3

, insert .... on duplicate key... , , name email - , auto_increment .

PHP, , - - , .

: , , , , .

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

insert... duplicate..., . , , . users.id to ideas.userID, , , .

: (aka, ZOMG facepalm)

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

2: (, )

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

:

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)

3: $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);
+1

, , :

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

" MySQL". ;

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/ ( )

2

, ( );

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

I would like to suggest this solution using the IFNULL control function (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 should be something like that, but it helped me.

+1
source

More articles:


All Articles