PHP PDO分页查询：点击下一页按钮无响应问题排查

Hey there, let's break down why your next page button isn't doing anything—your current code is stuck always fetching the first record because you're hardcoding the starting offset. Let's fix this step by step.

The Core Problem in Your Current Code #

Your $start variable is set to 0 permanently. No matter how many times you click "next page", you're always querying from the first record. Plus, you're not handling the page number input from your frontend, which is essential for pagination.

Step-by-Step Solution #

1. Modify the getBooks Function to Handle Dynamic Page Numbers

First, update your function to accept a page number parameter, calculate the correct starting offset, and use proper PDO parameter binding (to avoid SQL injection):

public function getBooks($page = 1) {
    $limit = 1; // Number of records per page
    // Calculate starting offset: (current page - 1) * records per page
    $start = ($page - 1) * $limit;

    // Fix your incomplete JOIN syntax
    $query = "SELECT Library.nameOfBook 
              FROM loginUser 
              JOIN userBook ON userBook.user_id = loginUser.id
              JOIN Library ON userBook.book_id = Library.id 
              WHERE loginUser.username = :username
              LIMIT :start, :limit";

    // Prepare the statement (assume $this->pdo is your PDO connection)
    $stmt = $this->pdo->prepare($query);

    // Bind parameters for security and correct data types
    $stmt->bindParam(':username', 'loay', PDO::PARAM_STR);
    $stmt->bindParam(':start', $start, PDO::PARAM_INT);
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);

    $stmt->execute();
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

2. Add a Function to Get Total Record Count

To know if there's a next page (and avoid invalid page requests), you need the total number of matching records:

public function getTotalBooks() {
    $query = "SELECT COUNT(*) as total
              FROM loginUser 
              JOIN userBook ON userBook.user_id = loginUser.id
              JOIN Library ON userBook.book_id = Library.id 
              WHERE loginUser.username = :username";

    $stmt = $this->pdo->prepare($query);
    $stmt->bindParam(':username', 'loay', PDO::PARAM_STR);
    $stmt->execute();
    $result = $stmt->fetch(PDO::FETCH_ASSOC);
    return $result['total'];
}

3. Update Your Frontend to Pass Page Numbers

In your HTML, create pagination buttons that send the current page number via GET request:

<?php
// Get current page from URL (default to 1 if not set)
$currentPage = isset($_GET['page']) ? (int)$_GET['page'] : 1;

// Fetch books for current page (replace with your class instance)
$books = $yourClassInstance->getBooks($currentPage);
$totalBooks = $yourClassInstance->getTotalBooks();
$totalPages = ceil($totalBooks / 1); // 1 matches your $limit value
?>

<!-- Display books -->
<div class="books">
    <?php foreach($books as $book): ?>
        <p><?php echo htmlspecialchars($book['nameOfBook']); ?></p>
    <?php endforeach; ?>
</div>

<!-- Pagination buttons -->
<div class="pagination">
    <?php if($currentPage > 1): ?>
        <a href="?page=<?php echo $currentPage - 1; ?>">Previous Page</a>
    <?php endif; ?>

    <?php if($currentPage < $totalPages): ?>
        <a href="?page=<?php echo $currentPage + 1; ?>">Next Page</a>
    <?php endif; ?>
</div>

Key Notes to Avoid Issues #

• PDO Error Handling: Enable error mode for your PDO connection (e.g., $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);) to catch any query mistakes.
• Input Sanitization: Casting $_GET['page'] to int prevents invalid non-numeric values from breaking your offset calculation.
• Proper JOIN Syntax: Always define join conditions for each JOIN to avoid unintended cartesian products in your results.

Now when you click "Next Page", it'll pass the new page number to your PHP script, calculate the correct starting offset, and fetch the next record as expected.

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