Login with username or email

Question

I'm trying to enable my users to login with an email or a username. At the moment only the username is accepted. My working SQL looks like this:

$sql = "SELECT * FROM customers WHERE login = '".$username."' AND password = '".$password."'";

Info:

$username has the value of $_POST['username']
$password has the value of md5($_POST['password'])

Now I would like to extend it to the email address a customer can enter in his profile. My SQL looks like this:

$sql = "SELECT * FROM customers WHERE (login = '".$username."' OR email = '".$username."') AND password = '".$password."'";

I will check this SQL with:

$result = mysql_query($sql);    
mysql_num_rows($result)

But at the moment it doesn't work. If I use OR in my SQL, mysql_num_rows returns 0. What could be the problem? Or is there another and better way to achieve this?

Your code is fine, so something else is wrong. You need to run your queries directly on the database using phpMyAdmin or so to determine what is. Also never forget to check the result of a `mysql_query` call for success, and run `mysql_error` to determine the problem if there is one. — Niels Keurentjes, May 08 '13 at 09:12
All `mysql_` functions are deprecated. Also your code is open to [SQL injections](http://xkcd.com/327/). And md5 passwords are only slightly better than storing them in plain text. — Arjan, May 08 '13 at 09:13
What does "It does not work" mean? Give more details. Also, when getting problems with a query, always `echo $your_query` to know exactly what is sent to the database. — Jocelyn, May 08 '13 at 09:14
The code is ok, check you have entered right username or email and password — Vidya L, May 08 '13 at 09:15
@Jocelyn: Agree with you and @emjay also give echo output of `$sql` so that everybody can see — NullPointer, May 08 '13 at 09:15
Put the query directly in DB for example using phpmyadmin, the condition in query is ok And check this link http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php it will tell you why not to use mysql_* functions. But if you really want to use them write after mysql_query(); function mysql_error(); and write what it returns. — Robert, May 08 '13 at 09:16
Above SQL query is correct. May you have entered username/email, password wrongly and which doesn't exist in the customers table. — Logeshkumar, Feb 26 '20 at 06:37

score 0 · Answer 1 · answered May 08 '13 at 11:15

Try this : Username might have either username or email id right? So first check whether there is an "@" symbol in the username string.

That is

if(stripos($username,'@') !== FALSE){
$sql = "SELECT * FROM customers WHERE email = '".$username."' AND password = '".$password."'";
}else{
 $sql = "SELECT * FROM customers WHERE login = '".$username."' AND password = '".$password."'";
}

So if condition succeeds then email will be validated. Else login name will be validated

So you're assuming usernames cannot contain '@'? Feels like the wrong approach. — Hirnhamster, Aug 23 '14 at 21:12

score 0 · Answer 2 · answered Mar 22 '14 at 20:30

0

Try this one , I'm sure it will work :

$sql = "SELECT * FROM customers WHERE login = '".$username."' | email = '".$username."' AND password = '".$password."'";

And to determine when the password is wrong, or just login credentials :

add this :

if (mysql_num_rows($result)==0) {echo 'Wrong user name or password!';}

answered Mar 22 '14 at 20:30

Stormix

98
1
9

Did I get it right? Are you using bitwise operand (| - bitwise OR) inside SQL? I'm definitely missing something – Yaronius Sep 09 '15 at 15:18
Did u fix the problem or still having problems ? – Stormix Oct 25 '15 at 13:42

Red October · Answer 3 · 2013-05-08T10:41:42.313

-1

Maybe the problem is in the case of the characters?

SELECT * FROM `customers` WHERE (LOWER(`login`)='" . strtolower($username) . "' OR
LOWER(`email`)='" . strtolower($username) . "') AND `password`='" . $password . "'"

edited May 08 '13 at 10:41

answered May 08 '13 at 09:30

Red October

597
2
9
29

Yet you cannot determine when the password is wrong, or just login credentials. – May 08 '13 at 09:32
Why? `if (mysql_num_rows($result)==0) echo 'Wrong user name or password!';` – Red October May 08 '13 at 10:41
if `=` is case sensitive or `LIKE` is case sensitive depends on the collation used. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html – dognose Mar 22 '14 at 21:04

score -1 · Answer 4 · answered Dec 02 '13 at 07:55

 $sql = "SELECT * FROM customers 
         WHERE EXISTS(select 1 from customers where login = '".$username."'
                      OR email = '".$username."') 
               AND password = '".$password."'";

Try this,I hope your problem will be solved.

score -1 · Answer 5 · answered Jul 03 '14 at 11:57

-1

You can simply use 2 queries and the or in the condition

$query=mysql_query("select * from customers where user='$username' and password='$password'");
$query1=mysql_query("select * from customers where email='$username' and password='$password'");
if(mysql_num_rows($query)==1 || mysql_num_rows($query1)==1)
{
//YOUR CODE
}
else
{
//YOUR CODE
}

answered Jul 03 '14 at 11:57

user3801289

31
4

Why would you make 2 requests when you only need one? – Hirnhamster Aug 23 '14 at 21:11

score -1 · Answer 6 · answered Mar 16 '20 at 21:53

I know this question is old, but as it doesn't have an answer that fits the needs of the OP, here's a solution that worked out for me:

I used the filter_var() function with the FILTER_VALIDATE_EMAIL parameter.

if (filter_var($input, FILTER_VALIDATE_EMAIL)) {
  $type = "email";
} else {
  $type = "username";
}
$sql = hive_query("SELECT * FROM `customers` WHERE `" . $type . "` = '" . $input . "'");

Login with username or email

6 Answers6