PHP SQL Server函数COUNT查询返回0时变量未定义问题

问题描述 #

我正在开发一个检查用户是否保存登录凭证的PHP函数，当SQL Server查询返回count=0时，会出现“未定义变量$returnCount”错误，但查询返回count=1时能正常获取值。我是PHP新手，不知道怎么修复。

流程背景 #

• 用户登录网站时，将$_SESSION['logged_in']设为true；
• 用户勾选“记住我”选项时，会把唯一令牌remToken和用户注册时生成的userID存入Cookie，并将这两个值插入数据库。

函数目标 #

网站每个页面执行该函数，逻辑如下：

1. 检查$_SESSION['logged_in']是否已设置且为true，是则保持true，否则设为false；
2. 检查用户是否有活跃的remToken和userID Cookie：
   • 若存在则查询数据库中是否有匹配的活跃记录，匹配则设$_SESSION['logged_in']为true；
   • 若不存在匹配的活跃记录，则清除Cookie并设$_SESSION['logged_in']为false；
3. 最后返回$_SESSION['logged_in']。

当前问题 #

测试场景中SQL语句返回0时，执行echo $returnCount会提示“UNDEFINED VARIABLE $RETURNCOUNT”；当查询返回1时可正常获取值。$pdo变量配置正确，在其他查询中可正常使用。

函数代码 #

function cookie_RememberMeCheck($pdo) {
    //Function expectation:
    //If the session "logged_in" is not set and not true, set it to false
    //Next, check if the user has active "remember me" cookies. If they do, set the logged_in state to "true"
        //If the user has inactive "remember me" cookies, set "logged_in" to false

    //If the user has not logged in during this session, set the logged_in value to "false". If they have logged in, set the session to "true"
    if (isset($_SESSION['logged_in']) && $_SESSION['logged_in'] = true) {
        $_SESSION['logged_in'] = true;
    } else {
        $_SESSION['logged_in'] = false;
    }
    

    //Check if the cookies "remToken" and "userID" have been set
    if (isset($_COOKIE['remToken']) && isset($_COOKIE['userID'])) {

        //Set the values of the UserID and remToken so we can match them to the database
        $cookie_userID = $_COOKIE['userID'];
        $cookie_remToken = $_COOKIE['remToken'];


        $sqlStmt = 'select count(*) as RemCount from DessertBar..UserRemLogin rl with (nolock) where rl.UserID = :userID and rl.RemToken = :remToken and rl.isActive = 1';
        $sqlStmtPrepare = $pdo->prepare($sqlStmt);
        $sqlStmtPrepare->bindParam(':userID', $cookie_userID);
        $sqlStmtPrepare->bindParam(':remToken', $cookie_remToken);
        $sqlStmtPrepare->execute();
        $returnCount = $sqlStmtPrepare->fetchColumn();

        if ($returnCount > 0) {
            //If user has active cookies in database, set logged_in state to true
            $_SESSION['logged_in'] = true;
        } else {
            //If the user does not have active cookies in the database, destroy the cookies and logout the user
            unset($_COOKIE['userID']);
            unset($_COOKIE['remToken']);

            $_SESSION['logged_in'] = false;
        }

    }

    //Finally, return the result of the logged_in state for the user
    //return $_SESSION['logged_in'];
    echo $returnCount;
}

1. 修复变量未定义问题 #

$returnCount仅在Cookie存在的代码块内定义，如果用户没有remToken或userID Cookie，代码块不会执行，导致函数末尾echo $returnCount时变量未定义。

解决方法：在函数开头初始化$returnCount为默认值0：

function cookie_RememberMeCheck($pdo) {
    $returnCount = 0; // 初始化变量
    // 后续代码...
}

2. 修复会话检查的逻辑错误 #

原代码中isset($_SESSION['logged_in']) && $_SESSION['logged_in'] = true使用了赋值运算符=，而非比较运算符==/===，这会强制将$_SESSION['logged_in']设为true，导致逻辑错误。

修改为严格比较：

if (isset($_SESSION['logged_in']) && $_SESSION['logged_in'] === true) {
    $_SESSION['logged_in'] = true;
} else {
    $_SESSION['logged_in'] = false;
}

3. 正确删除客户端Cookie #

unset($_COOKIE['userID'])仅在当前PHP请求中清除了Cookie变量，不会删除客户端浏览器中的Cookie。需要通过设置过期时间来删除：

// 清除客户端Cookie
setcookie('userID', '', time() - 3600, '/');
setcookie('remToken', '', time() - 3600, '/');
unset($_COOKIE['userID']);
unset($_COOKIE['remToken']);

修复后的完整代码 #

function cookie_RememberMeCheck($pdo) {
    $returnCount = 0; // 初始化变量，避免未定义错误
    $_SESSION['logged_in'] = isset($_SESSION['logged_in']) && $_SESSION['logged_in'] === true; // 简化会话检查逻辑

    // 检查是否存在记住我的Cookie
    if (isset($_COOKIE['remToken']) && isset($_COOKIE['userID'])) {
        $cookie_userID = $_COOKIE['userID'];
        $cookie_remToken = $_COOKIE['remToken'];

        $sqlStmt = 'select count(*) from DessertBar..UserRemLogin rl with (nolock) where rl.UserID = :userID and rl.RemToken = :remToken and rl.isActive = 1';
        $sqlStmtPrepare = $pdo->prepare($sqlStmt);
        $sqlStmtPrepare->bindParam(':userID', $cookie_userID);
        $sqlStmtPrepare->bindParam(':remToken', $cookie_remToken);
        $sqlStmtPrepare->execute();
        $returnCount = (int)$sqlStmtPrepare->fetchColumn(); // 强制转为整数，确保类型正确

        if ($returnCount > 0) {
            $_SESSION['logged_in'] = true;
        } else {
            // 删除客户端Cookie
            setcookie('userID', '', time() - 3600, '/');
            setcookie('remToken', '', time() - 3600, '/');
            unset($_COOKIE['userID']);
            unset($_COOKIE['remToken']);

            $_SESSION['logged_in'] = false;
        }
    }

    // echo $returnCount; // 调试用，正式环境建议删除或注释
    return $_SESSION['logged_in']; // 恢复原本的返回逻辑
}

额外说明 #

• (int)$sqlStmtPrepare->fetchColumn()确保返回值为整数类型，避免因PDO返回字符串导致的潜在问题；
• 简化了会话检查的逻辑，用一行代码替代原有的if-else；
• 恢复了原本的return $_SESSION['logged_in']逻辑，符合函数设计目标。

内容的提问来源于stack exchange，提问作者AlpacaRG