How would I stop multiple emails OR username?

Question

I have the following code in my registration form to prevent against multiple usernames being created:

connect_db();
    $check = mysql_query("SELECT username FROM school_users WHERE username = '$username'") or die(mysql_error());
    $check2 = mysql_num_rows($check);

if ($check2 != 0) {
    respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}

However, I also want to check for email, in the same statement:

    connect_db();
        $check = mysql_query("SELECT username, email FROM school_users WHERE username = '$username' or email = '$email'") or die(mysql_error());
        $check2 = mysql_num_rows($check);

    if ($check2 != 0) {
if (???username???){
        respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}}
else if (???email???) {
    respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}}

Get the username and email from the query and check your post values against it? — hank, Feb 10 '13 at 17:49
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. — Kermit, Feb 10 '13 at 18:01
^^^^as true as it is, its getting a little tedious for regular S.O users to see it 10+ times a day — , Feb 10 '13 at 18:21

score 2 · Answer 1 · edited May 23 '17 at 12:12

You could try doing:

connect_db();
$check = mysql_query("SELECT 'User' validation
                        FROM school_users 
                       WHERE username = '$username' 
                       UNION ALL
                      SELECT 'Email' 
                        FROM school_users 
                       WHERE email = '$email'") or die(mysql_error());
$row = mysql_fetch_assoc($check);

if($row)
{
    if ($row["validation"] == 'User') {
            respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}}
    else if ($row["validation"] == 'Email') {
        respond("error", "Sorry, the email ".$_POST['email']." is already in use. Please choose a different email.");}}
}

OR you could just do it separately...

//Validate UserName
connect_db();
$check = mysql_query("SELECT username FROM school_users WHERE username = '$username'") or die(mysql_error());
$check2 = mysql_num_rows($check);

if ($check2 != 0) {
    respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}

//Validate Email
connect_db();
$checkEmail = mysql_query("SELECT email FROM school_users WHERE email = '$email'") or die(mysql_error());
$checkEmail2 = mysql_num_rows($check);

if ($checkEmail2 != 0) {
    respond("error", "Sorry, the email ".$_POST['email']." is already in use. Please choose a different email.");}

Additionally, your code is vulnerable to SQL Injection attacks and you are using deprecated MySQL php functions. If you do want to make your code better an less vulnerable, take a look at the following links:

Why shouldn't I use mysql_* functions in PHP?

What could i use instead of mysql_ functions?

Prepated Statements

Prepared Statements with MySQLi

I just showed a possible way of doing what the OP wants to. I also posted some other links that would help him make the code better, such as avoid using the mysql_ functions and prepared statements. The initiative is all his. — Mateus Schneiders, Feb 10 '13 at 18:11
A warning, for other peoples, which come to stack overflow as anon code finder, will be fine. — Gabriel Santos, Feb 10 '13 at 23:50

Gordon Linoff · Answer 2 · 2013-02-11T14:15:54.740

1

You can also do this in one statement:

SELECT username
FROM school_users
WHERE username = '$username' or  email = '$email'

If you do this, you'll need to change the message to the user . . . "Your user name or email (or both) are already in use".

To customize the message in one statement, you can use aggregation:

select (case when max(username = '$username') = 1 and max(email = '$email' = 1
             then 'both'
             when max(username = '$username') = 1
             then 'username'
             when max(email = $email) = 1
             then 'email'
        end) as WherePresent
from school_users
WHERE username = '$username' or  email = '$email'

This returns 0 rows if everything is ok. Otherwise, it returns one row with one of "both", "username", "email", indicating which is duplicated.

edited Feb 11 '13 at 14:15

answered Feb 10 '13 at 18:04

Gordon Linoff

1,242,037
58
646
786

I want to have a specific message for each. One if both, one if only user, one if only email. I just don't know how to catch later on for this. – user2058041 Feb 10 '13 at 18:19
And how would I go about executing this? still num_rows? – user2058041 Feb 10 '13 at 19:35
You would execute this the same way. If num rows is 0, then all is okay. Otherwise, you need to look at the value returned. – Gordon Linoff Feb 10 '13 at 19:57
Like... Where is the value returned? – user2058041 Feb 10 '13 at 21:05
In the result set. There should be a single row with a column named WherePresent (I just added the name). – Gordon Linoff Feb 11 '13 at 14:16

score 1 · Answer 3 · answered Feb 10 '13 at 18:21

The original code isn't that far from the solution as you might think.

$check = mysql_query("SELECT username, email FROM school_users WHERE username = '$username' or email = '$email' LIMIT 1") or die(mysql_error()); //Limit one reduces the time mysql searches the db since it stops on the first occurence
        $check2 = mysql_fetch_assoc($check); //we do not only want to know the count of matching rows, but the values return in email and username field

//trimming makes sure we do not have any spaces at the beginning or end
//strtolower makes sure we set UserName == usERnaME == username etc.pp.
if (strtolower(trim($row['username'])) == strtolower(trim($_POST['username']))){ // check the result
        respond("error", "Sorry, the username ".$_POST['username']." is already in use. Please choose a different username.");}
}
else if (strtolower(trim($row['email'])) == strtolower(trim($_POST['email']))) { // and again for email adress
    respond("error", "Sorry, the email ".$_POST['email']." is already in use. Please choose a different email.");}
}

I don't think I want to limit one. What if both the user and email is taken? — user2058041, Feb 10 '13 at 18:47
then the user will try two times anyways ;) No matter if you LIMIT or not, he always will.. the code for checking BOTH at the same time is more confusing than convenient ;) But as always: do as you like as long as it works it's okay :D — itsid, Feb 10 '13 at 23:32

How would I stop multiple emails OR username?

3 Answers3

Linked