How to handle pagination for MySQL result returned to jQuery/JSON object

Question

I have a query that currently looks like this:

$queryMovies = "SELECT
    m.movieID,
    n.title,
    m.directorID, d.directorFirstName, d.directorLastName,
    m.runTime,
    m.yearReleased,
    m.synopsis,
    m.formatID, f.format,
FROM movies m
LEFT JOIN directors d ON d.directorID = m.directorID
LEFT JOIN format f ON f.formatID = m.formatID
ORDER BY title";

The results are sent back to the jQuery $.ajax call like so:

$arr = array();

while($row = mysqli_fetch_array($resultMovies, MYSQLI_ASSOC)) {
    $arr[] = $row;
}
header('Content-Type: application/json');
echo json_encode($arr);

The success function of the $.ajax call handles the data something like this:

...
'<div class="synopsisCell">' +
    'Synopsis<br />' +
    '<label class="lblSynopsis">' + element['synopsis'] + '</label>' +
'</div>' +
'<div class="formatCell">' +
    '<label class="lblFormat">' + element['format'] + '</label>' +
'</div>' +
'<div class="yearCell">' +
    '<label class="lblYear">' + element['yearReleased'] + '</label>' +
'</div>' +
...

(As you can see that's an incomplete snippet of the full output)

I need to add pagination to the table that the data is being put into and I'm unsure of the best way to handle this. (I've never worked with programming for pagination before)

I started adding a separate query that just gets the number of records (to determine how many pages there will be), then I realized I still need to do a LIMIT clause with the start and number of records for the pagination, etc, etc.

I'm wondering two things at this point:

A) Could I in some way add the number of records to the existing query/return code, which I could then use on the jQuery side to build the pagination navigation (page number and next/prev/first/last page links).

or:

B) Could I maybe handle the pagination completely on the JS side by getting the number of records somehow from the JSON object and display only what set of results (which set of 20 records for example) to display in the table?

Also, when the user clicks on the pagination navigation links, will I have to re-run the query each time, or is there some way to "store" that info perhaps in the JS to avoid excessive server/database querying?

I'm not looking for specific coding, just maybe some pseudo-code to get me started in the right direction.

Thanks for anyone's help.

joshplien · Accepted Answer · 2013-03-12T03:09:45.247

A) It is possible to return the count and the limited results with one query using SQL_CALC_FOUND_ROWS. For performance reasons, it would probably be faster to use two separate statements.

B) This is also possible. You could get all of the results and hide/unhide the results depending on the page and not even reload the browser when a new page is selected. This may be an expensive approach if you are expecting a large result set.

If you want to limit your results on the database side:

// query, get result count
$count = ... COUNT(*)

// for pagination
$results_per_page = 20;  // arbitrary number
$total_pages = ceil($count / $results_per_page);
if(isset($_GET['page_num']) && $_GET['page_num'] > 0) {
  $page_num = $_GET['page_num']; // for example 2
}
else {
  $page_num = 1;
}
$upper_limit = $results_per_page * $page_num;
$lower_limit = $upper_limit - $results_per_page;

// query limited results
... LIMIT ".$lower_limit.", ".$results_per_page."

Hope this helps.

Thanks, bigbr. I'm leaning towards the JS-based paging solution, but the info on the php query helped, too. — marky, Mar 11 '13 at 09:53
For A performance, see discussions at [Which is fastest? SELECT SQL_CALC_FOUND_ROWS FROM `table`, or SELECT COUNT(*)](https://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count) — Ale, Jul 12 '20 at 11:23

score 0 · Answer 2 · answered Mar 10 '13 at 22:49

// on php
// $page, default is 1
$page = isset($_GET['page']) : $_GET['page'] : 1;
// $rows in page, default is 20
$limit = isset($_GET['limit']) : $_GET['limit'] : 20;
$offset = ($page - 1) * $limit;
$queryMovies = "SELECT
    m.movieID,
    n.title,
    m.directorID, d.directorFirstName, d.directorLastName,
    m.runTime,
    m.yearReleased,
    m.synopsis,
    m.formatID, f.format,
FROM movies m
LEFT JOIN directors d ON d.directorID = m.directorID
LEFT JOIN format f ON f.formatID = m.formatID
ORDER BY title
LIMIT $offset, $limit";

// can't remember if this going to work but this should
// give you an idea
$arr['config'] = array('current_page' => $page, 'limit' => $limit, 'total_number_of_rows' => 10000);

// do the json encoding

// in javascript you should use the info of
// the current_page to tell the user in what page he is
// using total_number_of_rows and limit, calculate the pages and show them to the
// user so he knows how many pages there are

How to handle pagination for MySQL result returned to jQuery/JSON object

2 Answers2