Проектная дилемма: Если адрес электронной почты уже используется, отправьте сообщение "Адрес электронной почты уже зарегистрирован", но не можете, потому что не можете добавить дубликат в таблицу

Question

Форма регистрации запрашивает имя пользователя и адрес электронной почты. После того, как данные проходят проверку, они добавляются в accounts Таблица. Он хранит такие данные, как имя пользователя, пароль и адрес электронной почты. Если имя пользователя уже занято, пользователь будет уведомлен, и никакие данные не будут добавлены в таблицу. Это было замечено как проблема безопасности, если пользователь был немедленно уведомлен, что адрес электронной почты уже был в таблице. Предложенное решение этой проблемы состояло в том, чтобы всегда отправлять подтверждающее электронное письмо, и если введенное имя пользователя уже существовало, электронное письмо сообщало бы, что "этот адрес электронной почты уже использовался" (и, конечно, не будет дана ссылка для активации).

Проблема в том, что, как это работает сейчас, если INSERT запрос не может вставить данные в таблицу, отображается сообщение "Имя пользователя занято". Это может быть неправильно, так как в таблице указан уникальный адрес электронной почты, поэтому запрос не будет выполнен, если будет введен тот же адрес электронной почты. Я больше не могу отправлять письмо с подтверждением, в котором говорится, что "этот адрес электронной почты уже использовался", поскольку в таблице нет записи, содержащей указанный адрес электронной почты.

Как я могу изменить систему, чтобы она работала?

Я использую MySQL и таблицу accounts имеет первичный ключ username уникальный ключ e-mail и атрибуты password а также activation,

if(mysqli_stmt_execute($createAccount))
    echo 'Username available!';
else
    echo 'Username unavailable!';

В SQL есть какой-то способ проверить, почему запрос не может быть вставлен в таблицу? Например, он может сказать, какой атрибут имеет повторяющееся значение?

Пожалуйста, дайте мне знать, если мой вопрос неясен.

Answer 1

Сначала выполните запрос SELECT, чтобы определить дубликаты. Если дубликаты не найдены, вы можете выполнить вашу ВСТАВКУ.

Answer 2

если вы хотите, чтобы БД выполняла требуемую проверку при вставке данных, и вы хотите различать неудачную попытку добавления электронного письма и неудачную попытку добавить имя пользователя, самое простое решение состоит в том, чтобы email стол и usernameТаблица. Ваш актуальный account таблица будет просто хранить внешний ключ для этих двух таблиц. Вот пример http://sqlfiddle.com/:

CREATE TABLE username (id INT AUTO_INCREMENT PRIMARY KEY NOT NULL, value CHAR(80) UNIQUE NOT NULL);
CREATE TABLE email (id  INT AUTO_INCREMENT PRIMARY KEY NOT NULL, value CHAR(80) UNIQUE NOT NULL);

CREATE TABLE account(id  INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
                     username_id  INT NOT NULL,
                     email_id INT NOT NULL,
                     FOREIGN KEY (username_id) REFERENCES username(id),
                     FOREIGN KEY (email_id) REFERENCES email(id) );

Добавление нового пользователя теперь будет выполняться в несколько этапов транзакции. Теперь вы сможете узнать, какой шаг провалился, если таковой имеется. И, находясь в транзакции, это сохранит атомарность создания учетной записи:

START TRANSACTION;
INSERT INTO email(value) VALUES ("[e-mail]");
INSERT INTO username(value) VALUES ("Sylvain");

-- you could obtain the ID programmatically by "last_insert_id"-like function 
INSERT INTO account(username_id, email_id) VALUES (1,1); 
COMMIT;

И запрос имени пользователя и электронной почты для данной учетной записи теперь потребует объединения:

SELECT username.value AS username, email.value AS email 
FROM username JOIN account ON username.id = account.username_id
JOIN email ON email.id = account.email_id
WHERE account.id = 1;

Я бы не сказал, что это лучшее решение, но, думаю, оно работает так, как вы хотите.