PDO查询用户关联书籍失败：登录验证正常但无书籍展示

Hey there! Your isAuthenticated() function works for verifying login credentials, but since it only checks if a matching user exists (via SELECT 1), it doesn’t give you the critical data needed to fetch their books. Let’s walk through the most likely issues and fixes:

1. Your Authentication Function Doesn’t Return the User’s ID #

Right now, isAuthenticated() only confirms a username/password pair exists—it doesn’t retrieve the user_id (the primary key of loginUser). You need this ID to query the association table later.

Fix the isAuthenticated() function to return the user’s ID and store it in the session:

public function isAuthenticated($username, $password) {
    $query = "SELECT id FROM loginUser WHERE username = :username AND password = :password";
    $statement = $this->db->prepare($query);
    $statement->bindParam(':username', $username);
    $statement->bindParam(':password', $password); 
    // Pro tip: Never store plaintext passwords! Use password_hash()/password_verify() for security
    $statement->execute();
    
    $user = $statement->fetch(PDO::FETCH_ASSOC);
    if ($user) {
        // Store user ID in session for cross-request access
        $_SESSION['user_id'] = $user['id'];
        return true;
    }
    return false;
}

2. You’re Missing Logic to Fetch the User’s Books #

Even with the user’s ID, you need a dedicated query to join the association table with the books table. Here’s how to build that:

public function getUserBooks($userId) {
    // Replace `user_book` with your actual association table name
    $query = "
        SELECT b.* 
        FROM books b
        JOIN user_book ub ON b.id = ub.book_id
        WHERE ub.user_id = :user_id
    ";
    $statement = $this->db->prepare($query);
    $statement->bindParam(':user_id', $userId);
    $statement->execute();
    
    return $statement->fetchAll(PDO::FETCH_ASSOC);
}

3. You’re Not Using Session Data to Target the Current User #

After login, you need to pull the user_id from the session to call the book-fetching function. Example workflow:

// After successful login check
if ($auth->isAuthenticated($username, $password)) {
    $currentUserId = $_SESSION['user_id'];
    $userBooks = $bookService->getUserBooks($currentUserId);
    // Now loop through $userBooks to render the books for the user
}

4. Double-Check Database Structure & Test Data #

• Confirm your association table has valid user_id and book_id values that match the primary keys of loginUser and books respectively.
• Ensure there are actual rows in the association table linking the logged-in user to existing books (empty data would result in no books showing!).
• Verify foreign key constraints are set up correctly (you’d likely see database errors if this was broken, but it’s worth checking).

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