PHP Pagination getting error when bind value

Question

I'm trying to use the pagination function to display 3 item per page. But i getting this error

Fatal error: Call to a member function bindParam() on boolean in C:\xampp\htdocs\cms\admin2\pagination.php on line 60

I had search for solution and they said that mostly is query problem that cause the error, but my sql query was not too complicated and the syntax used is correct, but i not sure why the error still coming out.

Here is my pagination code :

<?php
include "dbConnection.php";

global $dbLink;
try {

    // Find out how many items are in the table
    $total = $dbLink->query('
        SELECT
            COUNT(*)
        FROM
            sponsor_item
    ')->fetch_row()[0];

    // How many items to list per page
    $limit = 3;

    // How many pages will there be
    $pages = ceil($total / $limit);

    // What page are we currently on?
    $page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
        'options' => array(
            'default'   => 1,
            'min_range' => 1,
        ),
    )));

    // Calculate the offset for the query
    $offset = ($page - 1)  * $limit;

    // Some information to display to the user
    $start = $offset + 1;
    $end = min(($offset + $limit), $total);

    // The "back" link
    $prevlink = ($page > 1) ? '<a href="?page=1" title="First page">&laquo;</a> <a href="?page=' . ($page - 1) . '" title="Previous page">&lsaquo;</a>' : '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>';

    // The "forward" link
    $nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">&rsaquo;</a> <a href="?page=' . $pages . '" title="Last page">&raquo;</a>' : '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>';

    // Display the paging information
    echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';

    // Prepare the paged query
    $stmt = $dbLink->prepare('
        SELECT
            (*)
        FROM
            sponsor_item
        ORDER BY
            sponsor_item_id
        LIMIT
            :limit
        OFFSET
            :offset
    ');

    // Bind the query params
    $stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->execute();

    // Do we have any results?
    if ($stmt->rowCount() > 0) {
        // Define how we want to fetch the results
        $stmt->setFetchMode(PDO::FETCH_ASSOC);
        $iterator = new IteratorIterator($stmt);

        // Display the results
        foreach ($iterator as $row) {
            echo '<p>', $row['sponsor_item_id'], '</p>';
        }

    } else {
        echo '<p>No results could be displayed.</p>';
    }

} catch (Exception $e) {
    echo '<p>', $e->getMessage(), '</p>';
}
?>

What did I do wrong on the pagination page? Please point my wrong. Thanks

Answer 1

Pretty sure you have a typo in your query, the (*) in your SELECT clause should just be *.

Answer 2

$stmt is a boolean because prepare() failed. So you need to get used to using defensive coding practices.

// Prepare the paged query
$stmt = $dbLink->prepare('
    SELECT
        (*)
    FROM
        sponsor_item
    ORDER BY
        sponsor_item_id
    LIMIT
        :limit
    OFFSET
        :offset
');

if($stmt === false){
    // handle the error here 
}