PHP SQL SELECT WHERE AND

Question

I do not want to have to save the double user name and email in MySQL and now this code does not work, what is the problem?

How do I do that?

function isUserExist($email,$userName){
    global $connect,$tbl_users;
    $email = sanitize($email);
    $userName = sanitize($userName);
    $sql = ("SELECT `email` FROM `$tbl_users` WHERE `email`=? AND `user_name`=?");
    $result = $connect->prepare($sql);
    $result->bindValue(1,$email);
    $result->bindValue(2,$userName);
    $result->execute();
    if ($result->rowCount()>=1){
        return $result;
    }

    return false;
}




<?php
    $query = null;
    $query_exist = null;

    if (isset($_POST['btn_submit'])) {
        $query_exist = isUserExist($_POST['email'],$_POST['userName']);
        if ($query_exist){
            echo 'A user with this email has already registered with the site to change your email and try again';
        }
        else {
            $query = createUser($_POST['firstName'], $_POST['lastName'], 
            $_POST['userName'], $_POST['password'], $_POST['email'], 
            $_POST['mobile'], $_POST['role']);
        }
    }

Check select query here you use AND so if email and username both are same then return count > 0 otherwise return 0. — HP371, Nov 25 '19 at 11:48
@HP371 No, `if (null)`, `if (false)` or `if (true)` (which is the result of the current code) works just fine. You can check it like that. — Qirel, Nov 25 '19 at 11:49
What is `sanitize()`, and why do you use it? `$tbl_users` is also undefined in your code, and using a variable as a table-name can introduce security risks! — Qirel, Nov 25 '19 at 11:51
why use globals? Why not just pass those in as an argument? It makes for better code — delboy1978uk, Nov 25 '19 at 11:51
@Qirel `sanitize()` is probably some user defined function. The `$tbl_users` is not undefined because it's global variable and it's probably defined elsewhere. Using variables as table names doesn't introduce security risks as long as the variable is not populated from user's input. But it's definitively not best idea to use it like this because it will complicate maintaining the code in future. — Michal Hynčica, Nov 25 '19 at 12:24
Possible duplicate of [PDO/PHP - Check if row exist](https://stackoverflow.com/questions/11974613/pdo-php-check-if-row-exist) — Dharman, Nov 25 '19 at 12:46
We need to see what sanitize does. It probably damages the data. — Dharman, Nov 25 '19 at 12:48

score 0 · Answer 1 · answered Nov 25 '19 at 11:56

You should check separately for email and username, you use an AND clause right now, so the only way it will return true is if both the username and email match in the row, but you don't want that!

Try switching to an OR. That way it will return true even if the user doesn't match but the email does, or vice versa.

Also you can tidy your code a little to get rid of those globals:

function isUserExist($email,$userName, $connect, $tbl_users)
{
    $email = sanitize($email);
    $userName = sanitize($userName);
    $sql = "SELECT `email` FROM `$tbl_users` WHERE `email`=? AND `user_name`=?";
    $result = $connect->prepare($sql);
    $result->bindValue(1,$email);
    $result->bindValue(2,$userName);
    $result->execute();

    return $result->rowCount() >= 1;
}

score 0 · Answer 2 · answered Nov 25 '19 at 12:07

The problem is probably rowCount() method. This method is not guaranteed to work with SELECT queries. It returns the number of rows affected by last INSERT, UPDATE or DELETE query.

From PDO documentation:

For most databases, PDOStatement::rowCount() does not return the number of rows affected by a SELECT statement. Instead, use PDO::query() to issue a SELECT COUNT(*) statement with the same predicates as your intended SELECT statement, then use PDOStatement::fetchColumn() to retrieve the number of rows that will be returned. Your application can then perform the correct action.

So your code that check existence should look like this:

$sql = ("SELECT COUNT(*) FROM `$tbl_users` WHERE `email`=? AND `user_name`=?");
$result = $connect->prepare($sql);
$result->bindValue(1,$email);
$result->bindValue(2,$userName);
$result->execute();
return $result->fetchColumn() > 0;

PHP SQL SELECT WHERE AND

2 Answers2