CODEWITHSUNDEEP
phpmysqlsqlpdo
P_95
P_95

Reputation: 145

Multiple ways to identify user / mysql, pdo, php

I have a database table users. Every user has at least userid. Two other optional values are email and username (must contain letters also so it's never mixed to userid! - I know). They're all unique.

I have one method for getting details (row) from users table. Param $userIdentifier can be userid, email or username.

What I want, is that the query would work for all 3 types (mostly it's userid).

$user->getData(111203432);
$user->getData("[email protected]");
$user->getData("Admin");

if email: *WHERE email=...*
if userid: *WHERE userid=...*
if username: *WHERE username=...*

Right now my query is not working (and checking for usernames is missing). I have tried various things, this is the latest:

if(is_numeric($userIdentifier)) {
    // also tried if_int
    $sql_getUserByValue = "users.userid";
    $bindType = PDO::PARAM_INT;
} elseif(filter_var($userIdentifier, FILTER_VALIDATE_EMAIL)) {
    $sql_getUserByValue = "users.email";
    $bindType = PDO::PARAM_STR;
} else {
    return false; // user not found
}

global $main_dbconnection;

$query = $main_dbconnection->prepare("SELECT * FROM users 
    ..... some inner joins here because different user types in diff. tables ...
    WHERE $sql_getUserByValue=:userIdentifier");

$query->bindParam(':userIdentifier', $userIdentifier, $bindType);
$query->execute();
// Fetch result if found >>>

Not also giving any errors. Just 0 rows found (method returns false).

Any ideas how I could do this?

Upvotes: 2

Views: 52

Answers (3)

max
max

Reputation: 102055

SELECT * FROM users
...
WHERE (email = ? OR userid = ? OR username = ?)

Upvotes: 1

chiastic-security
chiastic-security

Reputation: 20520

You can just use OR in your query:

SELECT * FROM users ... WHERE email=... OR userid=... OR username=...

Upvotes: 1

Konerak
Konerak

Reputation: 39763

Unless the users table is HUGE, or really slow, you might be better to skip autodetection and use one single query for all:

SELECT * FROM USERS WHERE users.userid = x
UNION
SELECT * FROM USERS WHERE users.email= x
UNION
SELECT * FROM USERS WHERE users.username = x

Or in one query, but might be slower (depends on your indexes)

SELECT * FROM USERS WHERE users.userid = x
OR
users.email= x
OR
users.username = x

Upvotes: 1

Related Questions