Ajax Based Paginator– Spare your server some resources

The traditional php pager
->each time you load a new page
A) Determine the number of entries per page (it’s up to you just decide! let’s call it number_per_page ) )
B) poll the entire table with SELECT count(*) …. to determine total number of records (let’s call this total_records)
B) calculate the total number of pages ( total_records /number_per_page ) let’s call this total_pages
C) figure out your current page (something like $_GET[‘page’] * number_per_page )
C) do a second query  pull only the needed rows using MySql’s LIMIT

With this approach, you always

query the DB twice each page and do not remember  total_records 

never remember previously fetched queries (ok…there are server side caching solutions to consider)

With an Ajax based pager, you can take advantage of the fact that you’ll not be reloading the entire page–
this will allow us to do things

 1) only the poll the entire table once to calculate total_records–during the first time we connect and just store this number in a javascript variable
(Yes, I know there  are ways to do this server side with sesions etc–but that’d certainly be more server intensive than storing
the var on the  user’s machine in a javascript variable–I always like to foist off as much cpu on to  the user as possible!) Imagine the difference here with thousands of users online simultaneously  and cutting out a single query for each one .
  As an aside, I’ve read the select count(*) is instantaneous so carries little overhead-
-but what if you’re select count(*) is a 6 table MyIsam join–even if that join is optimized with indices, it won’t be instantaneous and will cause some locking) It’d still be nice not to have to run the join every time you page)

 2) Also, we, with AJAX, we will be able to cache previously visited pages into an hash array so if a user goes from the first page to the second
but then back to the first page…we can just use our cache (basically memcache ajax style). Again, there are server side equivalents…like memcache!…but let’s keep giving our users as much of the burden to bear as possible!

I. The AJax Pager

Demo can be seen here on my site math games.com  — look on the right for the ‘recent high scores’

the concept is pretty much the same as the server side one
variables required
var total_records //self explanatory
var numberPerPage //self explanatory
var nextStartAt   //representing the number the next query will start at…ie the MySql will be LIMIT nextStartAt,numberPerPage
1) The pager  : 2 functions
 load_first_page() -> this loads the first page and reads the ‘total_records’ variable that is sent back from the database. I simnply but ‘total_records’ into a hiddent input field.
 This function here is pretty much the only difference from the traditional server side method.

2) getNextPage(whichWay) -> just send your ajax based requests and load them into the html ( I use  .innerHTML). Psedo code
  if(whichWay ==’back’)
   nextStartAt -=  numberPerPage ; 
  else
   nextStartAt += numberPerPage ; 

 var url =’somesite . com?somequeryString=1&start=nextStartAt&numberRecords=numberPerPage’ 

 getAjaxBasedURL(Url) -> now,use JQuery or whatever you like to handle your Ajax request and response

 

II. Creating the Cache
We’re just going to create a a hashmap–ie an associative array whose hashes represent the
unique query of each ajax call
  my hashmap code looks like

var cache = new Array();

function getAjaxBasedURL(Url){
  
  
 var key = mode + ” + nextStart + ” + numPerPage  +” +countryNumber ;

  // key must be ‘unique’ so your hash table has no collisions.  

 if( cache[key] != null){  //if in cache just load the cache
  loadDataIntoPage(cache[key]);
 }
 else
  // write code that fetches, then displays your new url’s data onto the current page
  // after your store on current page
  //store it into cache[key]
}

/////////

The only other functionality that must be implemented is the following. You must tell PHP to calculate the total records during the first page’s request, and then of course send back the result of this query to store in javascript.