CODEWITHSUNDEEP
mysql
Mehdi Maazi
Mehdi Maazi

Reputation: 51

MySQL query with single argument and multiple data types

I have a table named "users" in MySQL with these data(data types):
id(10 digit INT), email(char-32), phone(13 digit INT), password(char-64)
I need to build a login page that accepts id/email/phone as username. Then it should search the table for any match and then check the password.
My query for getting data is this:

SELECT id, email, phone, password FROM users WHERE username IN (id, email, phone);

The problem is that when I try to search for email, it returns correct AND NULL records.
What am I doing wrong?
Sorry for my poor English.

edit:
I can check for username type in php then execute a type-specific query. But is there a way to use a single query to do it?

Upvotes: 1

Views: 93

Answers (2)

tadman
tadman

Reputation: 211740

Presuming these values are all unique then:

SELECT id, email, phone, password FROM users WHERE ? IN (id, email, phone) LIMIT 1

Where ? is a placeholder value you bind the given user input to.

Note: In practice you really don't want to do this, instead have the input type examined and typed according to patterns, such as through a regular expression, and then run a query appropriate for that type. Doing it as "one query" actually exposes you to collision problems where someone puts in a phone number identical to someone else's ID just to screw with them.

Upvotes: 1

Grace
Grace

Reputation: 11

If I understand your question that'username' is a parameter from login page, try this query

SELECT id, email, phone, password FROM users WHERE concat(id, email, phone)= username;

Upvotes: 1

Related Questions