Filtering and Sorting a Table with PHP

I had a friend ask me earlier this week if I knew of any resources that could point him in the right direction on how to create a PHP page of tabular data that had the ability to be both filtered and sorted. We both weren’t able to find anything suitably simplified so the underlying concepts are easily understandable. This article attempts to fill that gap.

Getting Started#anchor

I want to start out with a bit of a description of what the following code is meant to do and how it’s accomplished. This is — by design — an extremely bare-bones example, meant to be understood rather than duplicated verbatim. I went with a no JavaScript solution here but the act of filtering and sorting would likely feel quicker and more natural if JavaScript was used. If you want to follow along at home I’d suggest downloading this zip of all the (three) files necessary to get started. In that zip you’ll find a SQL file of the single table that holds all the data we’ll be using, an include with a single function where most of the magic happens, and a small index file to see the resulting magic unfold.

The Data#anchor

No tricks here, just some simple data to illustrate our purposes. The example is of a fictional three day seminar that has scheduled sessions at set times throughout the three days. Each session is meant for a specific skill level and covers one of several categories. It’s a straight SQL dump so you can use PHPMyAdmin/Navicat/etc. to create and upload the data so you can play along as you read. Here’s a couple sample rows of that data:

The Magic Function#anchor

This is where we get to the real meat ‘n’ potatoes, I’m introducing it early because it’s necessary to know how this works before discussing the next couple sections. The function below is located in the zip in the filtersort.inc.php file.

<?php

/**
 * Function to gather and return the appropriate results based on the filters received
 * @param type $filter - Array of filters to apply to the query
 * @param type $sort - Array of sorts to apply to the query
 * @return array $return - An array of filtered results
 */
function filtersort_query ( $filter = array(), $sort = array() ) {

	// Starter Variables
	$filter_sql_array = array();
	$return = array();
	$sort_sql_array = array();

	// Default Filter and Sort query additions, if the parameters are empty these will be the fallbacks that are used.
	$filter_sql = '';
	$sort_sql = 'category ASC';

	// Organize the filter additions to the query
	if ( isset($filter) && count( $filter ) > 0 ) {
		foreach ( $filter AS $filter_key => $filter_item ) {
			$filter_sql_array[] = '`'. $filter_key .'` = "'. $filter_item .'"';
		}
	}
	// Organize the sort additions to the query
	if ( isset($sort) && count( $sort ) > 0 ) {
		foreach ( $sort AS $sort_key => $sort_item ) {
			$sort_sql_array[] = '`'. $sort_key .'` '. $sort_item;
		}
	}

	// Build the query, add the filters and sorts to their proper places
	$query = mysql_query('
		SELECT
			*
		FROM
			sessions
			'. ( count( $filter_sql_array ) > 0 ? 'WHERE '. implode( ' AND ', $filter_sql_array ) : $filter_sql ) .
		 	( count( $sort_sql_array ) > 0 ? 'ORDER BY '. implode( ', ', $sort_sql_array ) : $sort_sql )
	);

	while ( $row = mysql_fetch_array($query, MYSQL_ASSOC) ) {
	    $return[] = $row;
	}

	return $return;
}

?>

This filtersort_query function accepts two associative arrays as parameters for filtering and sorting respectively. The $filter array keys are the table columns to filter and the values are the values we want to keep while filtering all others out. The $sort array keys are the table columns to sort and the values can be ‘ASC’ or ‘DESC’ to sort either in ascending or descending order. The function takes each of these arrays and converts each key/value pair into what will be their parts of the MySQL query.

Note: It’s recommended that if your table data is expected to be long that you also add some sort of pagination to the query/page to maintain a decent page load.

The query itself starts as a select of the whole table, so on initial page load all results are displayed. The filters and sorts are added if they’re present in the function parameters explained above. I’ve also added a default filter and sort in case you want to have some defaults to fall back on if the arrays are empty. These are the two variables called $filter_sql and $sort_sql.

Filtering#anchor

The filtering is done by a form located at the top of the index page, in my example they’re all select boxes but this could be extended to incorporate radio buttons and text inputs to suit your needs.

When the form is submitted, the index.php example will iterate through the returned form fields and set up an associative array for filtering. The array holds the same key/value pairs as is provided by the form and any with an empty value are weeded out and not sent.

// Starter Variables
$filter = array();
$sort = array();

// If we have GET variables, try to filter/sort
if ( isset($_GET) && count($_GET) > 0 ) {

	if ( isset($_GET['sort']) && strlen($_GET['sort']) > 0 ) {
		$sort[ $_GET['sort'] ] = 'ASC';
	}

	foreach ( $_GET AS $getkey => $getvar ) {
		if ( $getvar != "" ) {
			$filter[$getkey] = stripslashes($getvar);
		}
	}
	unset($filter['sort'], $filter['submit']);
}

Sorting#anchor

I believe that sorting is meant for after filters are set, as a way to more easily find the filtered result for which you’re searching. Going with this assumption, sorting takes the previously set key/values of the filters (explained above) and tacks on one more variable ‘sort’. This is all added to the href of a link tag on each of the headlines, so clicking on a headline sorts by that column. In this example I’m only sorting in ascending order so that’s the only thing added, if you wanted to add some sort of cycling between sorting in ascending and descending orders this would be where you’d put it. You can see the code that adds the sort to the Magic Query in lines 19-21 of the snippet directly above this section. This is where the order (set as ‘ASC’) is hard coded for all columns.

Putting it All Together#anchor

The table starts as a full list of all records in the table, selecting any value in the filter dropdowns filters to records that have only that value in that column, after submitting a filter you can click on a headline to sort the filtered data by that column in ascending order.

As mentioned above, adding some JavaScript to help with the filtering and sorting without page reload isn’t a bad idea. Pagination is another common addition especially for tables that can span thousands or more records. Also, building up the sort method might be helpful as well. For example adding the ability to sort by multiple columns and/or allowing for sorting in descending order. Finally, this could be updated to also include a keyword search, giving the end user even more flexibility on narrowing the results to find what they need. Sky’s the limit with an application like this, so I’d recommend spending some time determining your requirements early on and plan accordingly.