Grow your CSS skills. Land your dream job.

Simple PHP Pagination

  • # August 24, 2014 at 1:11 pm

    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?

    # August 24, 2014 at 1:50 pm

    Don’t copy this exactly – you’ll need to use the $_GET['page'] variable in calculating the LIMIT offset and putting appropriate links in, but it demonstrates the principles:

    // Include database connection file
    require_once("php/db-connect.php");
    
    // get database data
    try {
    
        $query = "SELECT SQL_CALC_FOUND_ROWS ID, TITLE, COVER_URL FROM movies ORDER BY id DESC LIMIT 0, 30";
        $stmt = $connect->prepare($query);
        $stmt->execute();
        $movies = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
        $query="SELECT FOUND_ROWS()";
        $stmt = $connect->query($query);
        $count = $stmt->fetch();
    
    } catch( PDOException $e ) {
    
        echo "Could not retrieve data from the database";
        die();
    
    }
    
                    <div>
                        <?php foreach ($movies as $movie): ?>
                            <div><a>"><img />" alt="<?php echo $movie['TITLE']; ?>" width="151" height="227" class="cover-image"/><h4><?php echo $movie['TITLE']; ?></h4></a></div>
                        <?php endforeach; ?>
                    </div>
    
                    <?php if($count[0] > 30): ?>
                        <a href="?page=2">Next page</a>
                    <?php endif; ?>
    

    I don’t know what tutorials you have tried, or what solutions you have seen on there. However, you should use native functions. It’s what they’re there for. MySQL Documentation on FOUND_ROWS()

    __
    # August 24, 2014 at 2:04 pm

    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.

    # August 24, 2014 at 3:57 pm

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

    # August 24, 2014 at 6:37 pm

    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.

    __
    # August 24, 2014 at 8:00 pm

    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.

    # August 25, 2014 at 5:53 am

    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.

    __
    # August 25, 2014 at 7:34 am

    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.

    # August 25, 2014 at 9:37 am

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

    __
    # August 25, 2014 at 9:38 am

    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;
    
    # August 25, 2014 at 2:38 pm

    Thank you @Traq I finally got it working.

    __
    # August 25, 2014 at 3:05 pm

    glad to hear it.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.

*May or may not contain any actual "CSS" or "Tricks".