Split error message of email or username already exist

Question

I can't figure out how to get 2 separate error messages for the email and username already exists. I want it to give the exact error, so if the email already exists, it will only say "email already exists, please choose another!" instead of "username or email already exists, please choose another!"

if ($stmt = $con->prepare('SELECT id, password FROM accounts WHERE username = ? OR email = ?')) {
    $stmt->bind_param('ss', $_POST['username'], $_POST['email']);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->num_rows > 0) {
        // Username already exists
        echo 'Username or email already exists, please choose another!';
    } else {
        // Insert the new account
        ..

you will have to run multiple queries to do that. Let me spin up quick mock code for you. — Ashish Patel, May 08 '20 at 22:58
Return username and email from the query and then test which one matches? — gre_gor, May 08 '20 at 23:03
@AshishPatel sorry, I'm new to Pdo and not sure what to do, you have an example? — AryTuber, May 08 '20 at 23:45
From a security point of view it is **BAD PRACTISE** to tell anyone that an email already exists on any system. This message should be deliberately vague. — Martin, May 09 '20 at 09:56

score 3 · Answer 1 · answered May 09 '20 at 00:36

First of all, if you want to check if a row exists in a database you do not need to fetch the data, you can simply fetch COUNT(*). However, in your situation, this is actually bringing you a step forward to what you want to achieve.

$stmt = $con->prepare('SELECT username, email FROM accounts WHERE username = ? OR email = ?');
$stmt->bind_param('ss', $_POST['username'], $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    if ($row['username'] === $_POST['username']) {
        // Username already exists
        echo 'Username already exists, please choose another!';
    } elseif ($row['email'] === $_POST['email']) {
        // Email already exists
        echo 'Email already exists, please choose another!';
    }
}

On a side note, please do not wrap prepare() method calls in if statements. You should enable proper error reporting instead. How to get the error message in MySQLi?

If you want to convert this code into PDO it's a different story. You can't mix these two APIs.

bestprogrammerintheworld · Answer 2 · 2020-05-09T09:41:53.047

Everything important about the DB @Dharman have already said and this code is based on Dharman's code but with another approach for doing the same thing with the actual error messages.

My approach requires that the input-field's name is the same name as the name of the db-fields, e.g. <input name="email> requires email field in db, <input name="username"> requires username field in db etc.

If you would like to show all of the messages:

$stmt = $con->prepare('SELECT username, email FROM accounts 
WHERE username = ? OR email = ?');
$stmt->bind_param('ss', $_POST['username'], $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    $errors = array_intersect($_POST,$row);
    foreach($errors as $key=>$item) {
        echo "$key already exists, please choose another<br>";
    }
}

If you just want to show one of the messages (the first), you could simply do a break in the loop...

$stmt = $con->prepare('SELECT username, email FROM accounts 
WHERE username = ? OR email = ?');
$stmt->bind_param('ss', $_POST['username'], $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    $errors = array_intersect($_POST,$row);
    foreach($errors as $key=>$item) {
        echo "$key already exists, please choose another<br>";
        break;
    }
}

...or just fetching the first key in the array $errors:

$stmt = $con->prepare('SELECT username, email FROM accounts 
WHERE username = ? OR email = ?');
$stmt->bind_param('ss', $_POST['username'], $_POST['email']);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
    $errors = array_intersect($_POST,$row);
    if (!empty($errors)) {
        echo array_keys($errors)[0] . " already exists, please choose another";
    }        
}

Split error message of email or username already exist

2 Answers2