用于插入记录的MySQL条件（如果不存在）[重复]

Question

This question already has an answer here:

• How to 'insert if not exists' in MySQL? 9 answers

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.

</div>

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