(Tutorials) Pagination

March 12, 2010

Use pagination: a bit (overly) complex but fast..

Purpose:

  • present database rows using pagination to navigate through the data
  • make number of rows and pages variable
  • prepare for optimalisation (“make it fast”).
  • make rows sortable


To make things more clear let’s first look at the final result1.
And this is another one: final result2.
(For both examples: don’t mind the ugly scaled images..)

Solution:

  • use php to do the database (mysql) lookups
  • use javascript to keep track of pagination
  • divide the rows in pages (i.e. show 10 rows per page, see final result)
  • divide pages in blocks (i.e.: show 10 pages per block in your pagination header, see final result.). I didn’t want to make a database query for every page and decided to query more pages (one block) at once and use javascript to make one page visible and the rest hidden.

Some words about the dividing in pages and blocks:
When asking data from a server always keep two things in mind: speed of presenting your data and server load.
Now this really depends on the data you have. When you don’t have many rows and the data is small you can of course load all data at once. But if you have many rows it’s better to make server calls for blocks of data. If your data per page is very huge (like using big images or many rows, or both) it may even be better to make server calls “per page”.

In this example we do not load all data at once or by page but by block. Then we display only the data for a certain page and not all data for a whole block we got from the server. This hiding of data is done using CSS (display: none).
This way we have data already in the DOM. So when going to the next/prev page the data is already there and makes the navigation pretty fast 🙂 We only (well, almost only…) make a new php call to the database when using fast forward/rewind (“>>” or “<<“).

Two important divs are used:

  • a div with id = “pagination“. This div will contain our pagination data to let us navigate through the pages.
  • a div with id = “main“. This div contains the table with the rows from the database which presents our data.

First let’s look at the data. The table layout for our example (“final result1”) is quite simple:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| imagepath   | varchar(200) | NO   |     | NULL    |                |
| title       | varchar(200) | NO   |     | NULL    |                |
| description | text         | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

We start with our index.html:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<link rel="stylesheet" href="pagination.css" type="text/css">
<head>
<script src="http://colo.all-stars.nl/js/jquery-1.3.2.min.js"></script>
<script src="pagination.js"></script>                                  
<script type="text/javascript">
  $(document).ready(function(){
    sort = 'title'; // only on start
    id = 1;
    idfrom = -1; // only on start
    // change npages and nrows to you liking
    // npages = number of pages which make up one block (queried from the server)
    // nrows = number of (database) rows per page     
    npages = 10;
    nrows = 10;
    maxrows = -1; // only on start
    show_page(sort,id,idfrom,npages,nrows,maxrows);
  })
</script>
</head>

<body>
<div id="pagination">
</div>
<div id="main">
</div>
</body></html>

$(document).ready(function(){ is jQuery’s way to call a function after the DOM is ready (after everything is loaded in the browser).
When we start we don’t come from anywhere in the pagination so idfrom is declared negative (-1).
We also don’t know our rows in the database yet so we give max_rows also a negative (-1).
We start at page 1 (startid = 1) and the number of pages and the number of rows to show per page are both 10. These can be changed to your liking.

The two empty divs is where the pagination (in div “pagination”) and our table data (in div “main”) will be placed.
Most of the work is done through the javascript functions in pagination.js. The function which fires everything is show_page:

function show_page(sort,id,idfrom,npages,nrows,max_rows) {
  // change the div pagination
  // if max_rows = -1 (only at the start) we need to get it
  if (max_rows == -1) {
    var max_rows = $.ajax({ url: "max_rows.php.txt", async: false }).responseText;
  }
  change_pagination(sort,id,npages,nrows,max_rows);
  // change the old page from visible (style.display == 'table-row-group') to hidden (style.display = 'none')
  // if idfrom == -1 (start) there's nothing to collapse
  if (idfrom != -1) {
    collapse_oldpage(idfrom);
  }

  // calculate rest if id == lastpageid
  rest = npages;
  lastpageid = Math.ceil(max_rows/nrows);
  if ((id == lastpageid) && (id%npages != 0)) {
    rest = id%npages;
  }
  // get new rows from the database
  // do this only when we press next ('>', or '>>') or previous ('<', or '<<') or
  // if we press the first or last pageid and we come from another block
  if ( (id%npages == 1 && (id > idfrom || id <= (idfrom - npages))) ||
       (id%npages == 0 && (idfrom - id == 1)) || (id == lastpageid && (id - rest >= idfrom)) ) {
    // we need to get new rows from the database, show a "loading" message.
    $("#main").replaceWith("<div id="main">Loading new data...</div>");
    // get new rows
    // determine begin row for first page (id = clicked page)
    if (id == lastpageid) {
      if (id%npages == 0) {
        from = (id - npages) * nrows;
      } else {
        from = (id - (id%npages)) * nrows;
      }
    } else {
      if (id%npages == 0) {
        from = (id - npages) * nrows;
      } else {
        // we pressed 1, nextpage or nextblock.
        from = (id - 1) * nrows;
      }
    }
    // we need npages*nrows to display
    to = npages * nrows;
    // start the new div
    var newrows = '<div id="main">';
    // get all rows using an ajax-call to get_rows.php
    // get_rows.php return the table with the new rows
    // get_rows.php makes the correct page (=tbody) visible
    var rows = $.ajax({ url: "get_rows.php",
      data: "sort="+sort+"&id="+id+"&npages="+npages+"&nrows="+nrows+"&max_rows="+max_rows+"&from="+from+"&to="+to,
      async: false
    }).responseText;
    // close our div "main"
    newrows = newrows+rows+'</div>';
    // use jqeury's "replaceWith" to replace the existing div with id = "main" with the new data
    $("#main").replaceWith(newrows);
  } else {
    // we did not load new data
    // we already have hidden the old page (with collapse_oldpage(idfrom))
    // make page (= tbody) visible with the id which we clicked in the navigation div
    $("#tbody"+id).attr("style", "display:table-row-group");
  }
}

The pagination is rebuild with the javascript function change_pagination. The main div (with the rows from the database) is build (when necessary) with an ajax call to get_rows.php.
Don’t forget to read the comments in both change_pagination and get_rows.php.
The most important parts in change_pagination:

  • get the slow/fast rewind/forward correct:
    • do not show a fast rewind at the first pagination
    • do not show a slow rewind while at the first page
    • do not show a fast forward at the last pagination
    • do not show a slow forward at the last page
    • elements are not shown using CSS “visibility:hidden”. So while they are not visible they are still there for a stable alignment
  • make the last pagination correct: the last pages most likely won’t fill up the whole last pagination range. More clear: if for example we have 37 pages and the pagination is done per 10 pages the last pagination range is from 31 to 37 (and not from 31 to 31 + 10).
  • make the corrcect argumentlist for show_page, especially determine the correct id which identiefies the underlying page (= tbody”id”)
  • display “Results: x – y of $max_rows”

Some general php code is used in get_rows.php:

# --------------------------------------------------------------------
# init stuff
# --------------------------------------------------------------------
# override php.ini settings
require_once("init.inc");
# common functions
require_once("db_connect.inc");
require_once("functions.inc");

To get the pages and rows (displayed in a table) we make a call in get_rows.php to the php function show_table($order,$id,$npages,$nrows,$max_rows,$from,$to)
Don’t forget to read the comments in this function, most should be pretty obvious.
The most important parts are:

  • select the correct rows (using mysql’s “limit from,to”)
  • get the underlying pages (= tbody’s) correct

We are almost there: the accurate reader should have noticed the call to sorted_rows (in the php function show_table). Some row sorting is done using new calls to the database.

The simple stylesheet for the demo is: pagination.css

And as an exercise fro the user:

  • sorting can be nicer (ascending and descending options)
  • use client-side sorting (javascript) in stead of the current server-side (php) solution
  • make “Loading new data” more appealing (and even working in IE …)
  • make images clickable and show them in a nice “popup” like i.e. using lightbox or thickbox
  • add inline editing (i.e. using jQuery’s jeditable), adding and deleting of rows!
  • add a caching mechanism to get even more speed! 🙂
  • make number and type of columns more easy to customize..
  • …

Download all files here.

Have fun with it! 🙂

tags: , , ,

 
"I'd give a thousand dollars to be one of them there millionaires!"

Powered by Wordpress. Theme by Shlomi Noach, openark.org
© 1997 - 2022 KwaLinux Trainingen | Algemene voorwaarden | KvK: 10147727 | BTW-id: NL001873211B65 | Disclaimer