Wwwebber's Blog

Ajax Based Paginator– Spare your server some resources

Posted in ajax by wwwebber on August 17, 2009

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.

     

    Comments Off on Ajax Based Paginator– Spare your server some resources

    How To Delete from Multiple Tables in Mysql

    Posted in MySql by wwwebber on August 11, 2009

    $query1 = ‘DELETE FROM table1 where id=1’;

    $query2 = ‘DELETE FROM table2 where id=1’;

    Many MySql newbies don’t know that you can join delete just like select and updates. Here’s how you’d issue a single query in a join

    $betterQuery = ‘DELETE t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id= t2.id  WHERE  t1.id=1’;

    Same effect but you’re only query the datbase once rather than twice.

    Comments Off on How To Delete from Multiple Tables in Mysql

    Data-lists.com is a Scam

    Posted in Uncategorized by wwwebber on August 10, 2009

    I am writing this to so that others do not make the same mistake that I did. A brief summary

    1) I bought a database from data-lists(dot)com

    2) the site falsely advertisizes a 100% money back guarentee

    3) the data that I bought was unusable. They gave me a inconsistently formatted csv… For that reason, the data couldn’t be parsed into mysql.

    4) I explained my problems to the data-lists.com scammers . They did not respond to my emails and they did not refund my money.

    G0 here for a good US public schools database

    Comments Off on Data-lists.com is a Scam