PHP/MySQL: Ajax-Style Sorting and Pagination

I did sort and pagination functions by using MySQL, Oracle, Sybase in various projects, here summary the usage of PHP/MySQL’s sort/pagination which combine MySQL’s ‘limit’ plus PHP’s superglobal SESSION.

PHP’s $_SESSION[] and MySQL’s limit are very useful in Ajax-style pagination and sort refreshing.
To apply $_SESSION[], just start session at the very beginning of the codes:
$start_session();

Then consider to put the following in $_SESSION:
$_SESSION[‘query_sql’]: current query string.
$_SESSION[‘total_records’]: total records of the query, so don’t need to select count(*) every time.
Even super, you can put dynamic variables such as current_page, sort_by column into $_SESSION.

After assembly that, let’s list the steps:

A. sort:

We do this by the sequence of HTML -> JavaScript -> PHP.
(1) HTML:
in list screen, if want to sort some column, adding the following alike for this column:

<a href="javascript:void(0);" onClick="sort_data(event, '<?=$div;?>','NAME')">
<img src="images/up.gif" border="0" width="12" height="12" alt="up"></a>
<a href="javascript:void(0);" onClick="sort_data(event, '<?=$div;?>','NAME DESC')">
<img src="images/down.gif" border="0" width="12" height="12" alt="down"></a>

This will generate a reminder indication that here has a sort function: asc or desc.

(2) JavaScript:
in a prototype OO environment(inside a class), the sort_data should like this:

sort_data: function(e, div, sortby) {
    if (sortby) {
        this.url += '&sort='+sortby;
    }
    new Ajax.Updater(div, this.url, {
        method: 'get',
        evalScripts: true,
        onLoading: function() {
            // when searching, a searching icon displayed instead of sort icon.
            if(!(Prototype.Browser.IE)) {
                var t = e.element();
                $(t).replace('<img src="images/processing.gif" 
                    width="12" height="12" border="0"/>');
            }
        }
    });
},

This function can be re-written in many ways by using jQuery, or not in a OO env.

(3) PHP: in the control php script:

if (isset($_GET['sort'])) {
		$query = $_SESSION['query_sql'];
    $new_order = $_GET['sort'];
    if (eregi("order by", $query)) {
        $query = preg_replace("/order by.*$/i", " order by " . $new_order, $query);
    }
    else {
        $query .= " order by " . $new_order;
    }
    $_SESSION['query_sql'] = $query;
}

(4) PHP: The new query string generated, with the sort request.
By combining $_SESSION[‘query_sql’], and MySQL’s ‘limit’, use mysql_query() and mysql_fetch_array() to get the dynamic data.

B. pagination

page is a little complex, coz there are multi-choice in HTML which maybe include many page-links to allow user to click.
(1) HTML:

printf("<a href='javascript:void(0);' onClick="page_data(event,'$url=%d','%s')\">
    [Prev]</a>\n", ($current_page-1), $div);
printf("<a href='javascript:void(0);' onClick="page_data(event,'$url=%d','%s')\">
    [Next]</a>\n", ($current_page+1), $div);

(2) JavaScript (in a prototype OO environment):

page_data: function(e, div) {
    if (!$(div).visible()) $(div).show();
    new Ajax.Updater(div, this.url, {
        method: 'get',
        evalScripts: true,
  onLoading: function() {
      if(!(Prototype.Browser.IE)) {
          var t = e.element();
          $(t).replace('<img src="images/processing.gif" width="12"
             height="12" border="0"/>');
      }
  }
    });
},

(3) PHP:

if (isset($_GET['page'])) {
	$query = $_SESSION['query_sql'];
	...
	if (preg_match("/limit /i", $query)) {
		$query = preg_replace("/limit.*$/i", 
            " limit $row_no, ".ROWS_PER_PAGE, $query);
	}
	else {
		$query .=  " limit  $row_no, ".ROWS_PER_PAGE;
	}
	$_SESSION['query_sql'] = $query;
}

This is a simple summary of the sort and pagination, many details should be added for a production. Anyway, they work excellent with Ajax-style effect – partially refresh page when sorting and pagination.

One thought on “PHP/MySQL: Ajax-Style Sorting and Pagination”

Leave a comment