Practical way to paginate data with ajax

Question

I have a file index.php which works like this:

• connects the database, fetches a table, stores the result in an object that stores objects (member variables correspond to field names). So I will have all queried data stored in an object of the class DataList.
• I have a DataListPaginator which implements Iterator interface, which I use for pagination this way: $p= new DataListPaginator($d,1,10); where $d is of the class DataList, 1 is the page number, 10 is the items per page to show.
• at the bottom I have some page numbers for the actual pagination script which is an ajax solution coded in loadPaginator.js and paginator_ajax.php, the latter being called by ajax.

In the file paginator_ajax.php I have the following:

• include classes again
• estabilish db connection again
• fetch data again
• produce the view again
• update page numbers

My question is, how could I manage to simplify this php script not to query the database over and over if I already have the results queried and stored in a variable somewhere in index.php.

Answer 1

You are losing one of the benefits of pagination by trying to achieve this.

connects the database, fetches a table, stores the result in an object that stores objects (member variables correspond to field names). So I will have all queried data stored in an object of the class DataList.

What happens when this table has 10,000 records? One of the points of pagination is to only retrieve a small number say 10, 25 or 50 to keep database queries quick. Otherwise you lose efficiency and speed.

You should stop trying to implement it this way as it does not make sense, the request time to a server + getting a paginated result set of under 100 will be negligible if not the same as not querying the database at all. Provided you have an optimised query with indexes.

You should be paginating the result set and sending the current page and limit used back to the client, allowing them to increment the page count to get the next result set.

I recommend you rethink this strategy of pulling the whole table and then processing it in PHP as this is what a database is meant to do, not something that is not optimised for that purpose.

Answer 2

You could use JQuery DataTables to apply pages and sorting to your data. Simply create a <table> element containing your data and apply the DataTable to your <table>. Some settings will get you the features you want.

Word of caution: DataTables can get slow if you are loading a lot of data. Having too many columns and/or too many rows will bring some browsers to a crawl and possibly lock them up. At this point you are better off implementing the pagination on the server-side.

JQuery DataTables