Simple PHP Pagination

[Anonymous]

I want to add a very simple next and previous pagination to my results. I’ve followed a few tutorials but they are outdated and don’t really relate to my current code much.

`
<?php

// Include database connection file
require_once("php/db-connect.php");

// get database data
try {

    $sql = "SELECT ID, TITLE, COVER_URL FROM movies ORDER BY id DESC";
    $q = $connect -> query($sql);
    $q -> setFetchMode(PDO::FETCH_ASSOC);

} catch( PDOException $e ) {

    echo "Could not retrieve data from the database";
    die();

}

                <div class="movies-library-container">
                    <?php while ($row = $q->fetch()): ?>
                        <div><a href="movie.php?id=<?php echo $row['ID']; ?>"><img src="img/movie-covers/<?php echo $row['COVER_URL']; ? />" alt="<?php echo $row['TITLE']; ?>" width="151" height="227" class="cover-image"/><h4><?php echo $row['TITLE']; ?></h4></a></div>
                    <?php endwhile; ?>
                </div>

?>
`

So how can i add pagination if there are more than lets say 30 results, the next and previous button appears and moves on the next page of results?

[__]

First, you’ll need to add a limit clause to your query. limit <n> limits MySQL to retrieving a maximum of <n> rows. In the form limit <o>,<n>, you’re still limited to <n> rows, but they will start from row <o>. This is affected by what order the rows are in.

What I’m describing here will work fine in most cases, but doesn’t scale well if you have a very large number of rows. You’ll need to make sure the columns you use to order by are properly indexed as well.

Normally, I would use a prepared statement in a case like this, but it is difficult because of how PDO treats parameters (it treats everything as a string, which causes an SQL error because limits must be numbers: there are workarounds, but it’s needlessly complicated and not always reliable). We’ll use variables and just be extra-careful instead:

<?php

$resultsPerPage = 30;

// we can be sure that $offset will always be a non-negative integer, because we:
//    a) make sure only digits are in the <code>page</code> value
//    b) multiply by the $resultsPerPage integer
//    c) default to 0 (meaning no offset) otherwise
if(
    isset( $_GET['page'] )
    && ctype_digit( $_GET['page'] )
    && $_GET['page'] > 0
){
    $offset = ($_GET['page'] - 1) * $resultsPerPage;
}
else{
    $offset = 0;
}

$sql = "SELECT ID, TITLE, COVER_URL FROM movies"
    ." ORDER BY id DESC"
    ." LIMIT $offset, $resultsPerPage";

This way, you can control the “page number” (the offset) by passing a page parameter in the url. For example,

<a href="http://example.com/paginated-page.php?page=1">Page 1</a>

…will result in 1 - 1 = 0 * 30 = 0 = "LIMIT 0, 30",
page 2 will result in 2 -1 = 1 * 30 = 30 = "LIMIT 30, 30",
and so forth.

By extension, you can create “next” and “previous” links/buttons by adding or subtracting 1 from the “current” page number.

edit
Note, this does not do anything to determine how many “pages” of results might exist. BenWalker’s example is a great way to approach that (you could even do $totalPages = ceil( $count[0] / $resultsPerPage ); to find out how many pages you actually need for all results).

It also doesn’t make sure that the requested page has any results on it. For example, if you asked for page “0”, you’d get page 1 instead, but if you asked for page “1728409355” (and there are not that many results), you’d just get an empty result set.

[Anonymous]

@Traq Thanks. Ill try your solutions later and comment the results.

[Anonymous]

This is still very confusing for me. Is there some sort of PHP plugin or whatever you might call it that will make this easier for me? For some reason its taking me longer to grasp PHP than any other language.

[__]

A full CMS platform (e.g., wordpress) might have a plugin for pagination, but in general, no: it is too dependent on your database structure to handle in a single, one-size-fits-all thing.

The individual tasks can —and indeed, should— be written into functions so they are easier to use. If you’d like to try, I’d be happy to help. To get off to a good start, please share what version of php you are running, and any other relevant details (e.g., relevant DB schema, whether you plan to paginate anything else, etc.).

For some reason its taking me longer to grasp PHP than any other language.

If there’s anything specific that is holding you back, please let us know.

[Anonymous]

My PHP version is 5.4.31

the pagination will be for the all categories page, and about 10 other pages each being a different movie genre like action/adventure, comedy etc.

Thats pretty much it. Theres not much to it and the code in my first post is what displays the movie results that i want to paginate. My database is also very simple with nothing fancy.

[__]

the pagination will be for the all categories page, and about 10 other pages each being a different movie genre like action/adventure, comedy etc.

Do they select from different database tables, or all from the same table?

I’d start with two functions:

1. a function to determine the desired offset, based on the current page and the desired results per page
2. a function to write HTML markup for a collection of pagination links

The first is very simple — it’s basically just wrapping the code I showed above into a function. For the second function, you will need to decide what sort of finished HTML you want for the pagination links.

[Anonymous]

This is what im trying to paginate http://j-v.me/5starmovies/

[__]

The first function, to determine the offset for your query, would look something like this:

function pagination_getOffset( $page,$itemsPerPage ){
    // make sure both args are integers
    if(
        ! ctype_digit( (string)$page )
        || ! ctype_digit( (string)$itemsPerPage )
    ){
        return 0;
    }
    return ($page - 1) * $itemsPerPage;
}

The second would create your pagination links. Since you only want prev/next links, it’s pretty simple:

function pagination_html( $page,$totalPages ){
    // make sure both args are integers; current page cannot be greater than total pages
    if(
        ! ctype_digit( (string)$page )
        || ! ctype_digit( (string)$totalPages )
        || $page > $totalPages
    ){
        return false;
    }
    // this var will hold the HTML markup
    $html = '';
    // no "prev" link if on first page
    if( $page > 1 ){
        $html .= '<a href="?page='.($page - 1).'" rel="previous">Previous</a>';
    }
    // no "next" link on last page
    if( $page < $totalPages ){
        $html .= '<a href="?page='.($page + 1).'" rel="next">?Next</a>';
    }
    // done
    return $html;
}

Usage would be like so:

<?php
// determine requested page, default to page 1
$page = isset( $_GET['page'] )? 
    $_GET['page']: 
    1;

// decide # of items per page
$limit = 30;

// determine offset for SQL statement
$offset = pagination_getOffset( $page,$limit );

// write your sql
$sql = "SELECT SQL_CALC_FOUND_ROWS ID, TITLE, COVER_URL FROM movies"
    ." ORDER BY id DESC"
    ." LIMIT $offset, $limit";
// do your query, etc..
//  . . .

// use @BenWalker's suggestion to get total # of pages
$sql = "SELECT FOUND_ROWS()";
// do the query, assign result to $totalPages
//  . . .

// get pagination links
$paginationHTML = pagination_html( $page,$totalPages );

// print where desired on page
echo $paginationHTML;

[Anonymous]

Thank you @Traq I finally got it working.

[__]

glad to hear it.

[Author]

Posts