MySQL condition for inserting a record if not exists [duplicate]

Question

I'm trying to get the most work easier. This is a finding that the record in the database and eventually, if not, then create it.

Here is my command MySQL:

IF NOT EXISTS( SELECT * FROM USERS
               WHERE
                     name = '$this->m_name'
                 AND email = '$this->m_email'
             ) 
  BEGIN
     INSERT INTO `USERS` (`id`, `name`, `email`, `phone`, `text`, `type`)
     VALUES (NULL, '$this->m_name', '$this->m_email', '$this->m_phone', '', '');
  END

For reasons not known to me, it did not work.

Is there no chance that two customers could have the same name? I find that extraordinary. But I don't want to work either. — Strawberry
– Strawberry, Commented Jan 24, 2014 at 10:34
corrected from OR to AND, but it does not solve my problem. I'm wondering whether the registration conditions in mysql like this — user3218484
– user3218484, Commented Jan 24, 2014 at 10:39
You could use "INSERT ... ON DUPLICATE KEY UPDATE" dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html — JorgeeFG
– JorgeeFG, Commented Jan 24, 2014 at 12:40

MrMarlow · Accepted Answer · 2014-01-24 10:50:26Z

1

Personally, I'd consider looking of another way to tackle this.

As someone pointed out: if 2 people have the same name, which is possible, then you will have a problem.

However, if you have a UNIQUE KEY on both the name and phone values, MySQL itself will refuse to insert the row, so you don't have to.

ALTER TABLE `USERS` ADD UNIQUE (`name`,`phone`);

Then, if memory serves me correctly, the MySQL error number for duplicate row, which is the error it should show, is 1022. This means the code to handle the output should look something like;

$sql = mysql_query("INSERT INTO `USERS` (`id`, `name`, `email`, `phone`, `text`, `type`)VALUES (NULL, '$this->m_name', '$this->m_email', '$this->m_phone', '', '')");
if (mysql_errno() == 1022)
{
    echo 'It would appear that you already have an account with us. If you can not access your account, please reset lost password (link here)';
}

Hope that helps.

answered Jan 24, 2014 at 10:50

MrMarlow

8564 silver badges17 bronze badges

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

1 Comment

user3218484 Over a year ago

It is about creating customer who created the reservation. Therefore, each customer has created their ID even if they have identical names and email. So it's not the login records. This is just a user database which eventually sent the news. I had it done in php but I find it easier to make straight in SQL.

Collectives™ on Stack Overflow

MySQL condition for inserting a record if not exists [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related