php - 如何使用bind_result和fetch给变量赋值？

Question

我想从数据库中获取值并将它们分配给变量。这是我的代码。

    $stmt = $this->conn->prepare("SELECT ngno, email, encrypted_password, name, user_type FROM `guide` WHERE email = ?");
    $stmt->bind_param("s", $email);

    if ($stmt->execute()) {
        $stmt->bind_result($ngno, $email, $encrypted_password, $name, $user_type);
        $user = $stmt->fetch();
        $stmt->close();

        if($encrypted_password == $password){
            return $user;
        }
    } else {
        return NULL;
    }

我需要将表中的值分配给变量 $user 并将其返回到我的 login.php 文件。

$user = $db->getUserByEmailAndPassword($email, $password);

if ($user != false) {
    // use is found
    $response["error"] = FALSE;
    $response["ngno"] = $user["ngno"];
    $response["user"]["email"] = $user["email"];
    $response["user"]["name"] = $user["name"];  
    $response["user"]["user_type"] = $user["user_type"];
    echo json_encode($response);
} else {
    // user is not found with the credentials
    $response["error"] = TRUE;
    $response["error_msg"] = "Login credentials are wrong. Please try again!";
    echo json_encode($response);
}

我的代码似乎没有为 $user 变量赋值。我究竟做错了什么？

Answer 1

问题是由于 getUserByEmailAndPassword() 方法中的以下语句造成的，

$user = $stmt->fetch();

fetch() 方法将准备好的语句中的结果提取到绑定(bind)变量中，该方法实际上返回 true 或 false。

引用资料如下:

• mysqli_stmt::fetch

而是创建一个数组，例如 $user 并将这些绑定(bind)变量值插入数组中。最后从方法中返回数组。

所以你的 getUserByEmailAndPassword() 方法应该是这样的:

public  function getUserByEmailAndPassword($email, $password){
    $stmt = $this->conn->prepare("SELECT ngno, email, encrypted_password, name, user_type FROM `guide` WHERE email = ? LIMIT 1");
    $stmt->bind_param("s", $email);

    if ($stmt->execute()) {
        $stmt->bind_result($ngno, $email, $encrypted_password, $name, $user_type);
        $user = array();
        if($stmt->fetch()){
            if($encrypted_password == $password){
                $user['ngno'] = $ngno;
                $user['email'] = $email;
                $user['name'] = $name;
                $user['user_type'] = $user_type;

                $stmt->close();
                return $user;
            }else{
                return false;
            }

        }else{
            return false;
        }
    } else {
        return false;
    }
}