Mysqli check if 2 value exist

Hello

I’m trying to check if 2 values exist in the database using php for a registration script, Google didn’t help…

I need something like this :

if($stmt = mysqli_prepare($db_connect,'QUERY TO CHECK IF USERNAME AND/OR EMAIL EXIST')){
mysqli_stmt_bind_param($stmt, "ss", $user,$email);
mysqli_stmt_execute($stmt);
/*
    if username exist
        echo username exist
    elseif email exist
        echo email exist
*/
}
else{/*error*/}

thanks !

The query should be

SELECT username, email FROM users WHERE username = ? OR email = ?

Though I’m not too fond of procedural calls. I rather do it OOP style.

<?php
$db_connect = new mysqli('localhost', 'root', 'root', 'test');

$user = 'root';
$email = 'root@root.com';

$stmt = $db_connect->prepare('SELECT username, email FROM users WHERE username = ? OR email = ?');
$stmt->bind_param('ss', $user, $email);
$stmt->execute();
$stmt->store_result();

if($stmt->num_rows) {

    print('Exists');

} else {

    print('Doesn\'t exist');

}
1 Like

Thanks for the fast reply !
but how can I determine which one exists because I want to return different message for email and a different message for username

Email must have an @

$email = 'valid@gmail.com';
$atPos = strpos( $email, '@') ; // returns numeric (true) if  has @
if($atPos) {
    echo '<br>' .$email;
}else{
    echo '<br> Yes we have NO email :)';
}
1 Like

Then you would need to do separate queries because using the OR & AND clause basically means that the results should return 1 of the above. I’m not sure how preferred this is, but it’s what is at the top of my head.

That is true, but it’s best to use the filter_var($email, FILTER_VALIDATE_EMAIL); because someone can pass an email without the ending prefix. Like valid@ and strpos will still return a true statement, but it’s not a valid email. Using FILTER_VALIDATE_EMAIL will give you a better result. Since the email valid@ is not a valid email, it is forced to be nothing. You need xx@xx.com to make FILTER_VALIDATE_EMAIL to work. If you type in xx@xx, it will fail FILTER_VALIDATE_EMAIL.

1 Like

I think I got it :

if($stmt = mysqli_prepare($db_connect,'SELECT username, email FROM users WHERE username = ? OR email = ?')){
mysqli_stmt_bind_param($stmt, "ss", $user,$email);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
if(mysqli_num_rows($result) !== 0) {
$row = mysqli_fetch_assoc($result);
if($row['username']==$user){
    echo'username exist <br />';
}
elseif($row['email']==$email){
    echo 'email exist <br />';
}
}
else {
echo'username/email don\'t exist');
}
}
else {/*error*/}

Thanks for your help !

I don’t know as revealing that an email exists is such a good idea. Not so bad saying a vague generic “you need a different username email address combination”

But “email exist” says “that is a real email address”.

For example, say I try to register as
“New_user”, “Ahmad_Sqalli@gmail.com
And I see that, Now I can try to login as

Ahmad_Sqalli - Ahmad_Sqalli@gmail.com and have access to your account.

Or I could add Ahmad_Sqalli@gmail.com to my SPAM mailing list.

OK, maybe not likely to happen, just the same, something to think about.

2 Likes

That is indeed a correct statement. The one I gave was just a generic sample. Saying that the row exists, never said the username and email exists. It is probable that someone could use this to hack into someone’s account. Although if there are strict measures, this would actually be hard to bypass.

If there is some kind of authentication based on IP then based on recovery questions. Displaying that kind of message wouldn’t hurt.

1 Like

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.