在SQL语句中使用OR，在PHP中给出“未定义的变量”错误

问题描述：

I'm grabbing a count stat ($hits) from the following sql statement:

SELECT COUNT(*) AS hits FROM users 
    WHERE password = :password
    AND username = :username

Grabbing the variable in a while() loop here works fine, it's not untill I try this:

SELECT COUNT(*) AS hits FROM users 
    WHERE password = :password
    AND (username = :username OR email = :username)

that I get an 'undifined variable' error when I try pull 'hits' as $hits = $row['hits'];

I want a user to be able to log in using a username or email address. Can anyone tell me why the first SQL statement works fine but the second does not?

My PHP is as follows:

// COUNT HITS
$COUNT_HITS = $DBH->prepare("SELECT COUNT(*) AS hits FROM users WHERE password = :password AND (username = :username OR email = :username)");
$COUNT_HITS->bindParam(':password', $password);
$COUNT_HITS->bindParam(':username', $username);
$COUNT_HITS->execute();
while($row = $COUNT_HITS->fetch(PDO::FETCH_ASSOC)){
    $hits= $row['hits'];
}

答

Problem

Look here:

username = :username OR email = :username

You can do this in PDO, not by default at least

Solution

Add a third params

username = :username OR email = :username_email

and bind to the same $username value, that's okay.

$COUNT_HITS->bindParam(':password', $password);
$COUNT_HITS->bindParam(':username', $username);
$COUNT_HITS->bindParam(':username_email', $username);
$COUNT_HITS->execute();

答

Ok, here a proper answer :

$COUNT_HITS = $DBH->prepare("SELECT COUNT(*) AS hits FROM users WHERE password = :password AND (username = :username OR email = :email)"); 
$COUNT_HITS->bindParam(':password', $password);
$COUNT_HITS->bindParam(':username', $username);
$COUNT_HITS->bindParam(':email', $username);

Hope this works.

在SQL语句中使用OR，在PHP中给出“未定义的变量”错误

相关推荐