Sorting Your MySQL Results Set In PHP Using jQuery (And A More Traditional Approach)

Recently asked at Yahoo! Answers:

Sort ORDER BY links for each COLUMN of MYSQL database in PHP page?
These are the links I am currently using but they are just on the bottom of the page int he middle of no where…

<p>Sort By:</p>
<p><a href=”address.php”>address</a></p>
<p><a href=”email.php”>email</a></p>
<p><a href=”name.php”>name</a></p>
<p><a href=”phone.php”>phone</a></p>
<p><a href=”tvprogram.php”>tvprogram</a></p>

Additional Details
I currently have a form where users input their data (form.php). This information goes into MYSQL database (form_data). The information is then recalled by (userdata.php) for our advertisers to see. If they would like to sort by TVProgram or Name it has to be a new page, I believe. But how can I add that link to the new page with the new “ORDER BY” query. I have created some links on top that link to four different php pages, but how can I make those links be on the column header title things…PLease help!

This is a fairly common PHP question, since the built-in MySQL functions of PHP have never provided a simple way to paginate or sort a results set (that is, a way to sort by column values, or break into “pages”, the records you get back from a successful query).

There are a few ways to accomplish results set sorting. I’ll discuss two here: A traditional, PHP-based approach, and a more modern, JavaScript-based approach achieved via jQuery.

Aside: How PHP Works With MySQL Recordsets

Before I get into how to solve this problem, a quick aside on how PHP and MySQL interact.

When we make a successful database query in PHP, we receive back a result resource. As I’ve previously noted, this resource is basically a pointer to a memory store on the MySQL server, which is holding the records for us.

Consider this query:

$rs = mysql_query("SELECT * FROM table ORDER BY column");

If the query above fails, $rs is Boolean false. If the query is successful, $rs is a result resource: That is, $rs contains a reference to a memory location in MySQL where the records returned, if any, are being stored. (Even if no records are returned, a query is successful if the server can parse it; therefore, a result resource is always created for successful queries, even those with no records returned.)

Because $rs is only a reference to a memory location in the MySQL server, if we want to work with the records being stored on the MySQL server, we need to put those records into something PHP can actually work with; that is, some sort of local, structured data store.

Most folks use mysql_fetch_array() (or one of its siblings, such as mysql_fetch_assoc() or mysql_fetch_row()) to assign the rows held on the MySQL server to a PHP array. And most people do that within a while loop, iterating through each record stored on the MySQL server:

if(mysql_num_rows($rs) == 0) {
	echo "No records!";
}
else {
	//loop through all records in MySQL memory
	//assign each record to $row array
	while($row = mysql_fetch_array($rs)) {
		//echo column name / value pairs
		foreach($row as $key => $value) {
			echo "$key: $value";
		}
		//line break after each row
		echo "<br />";
	}
}

A Traditional, PHP-Only Approach

Because of the limits of the PHP language, which I just noted, traditional record sorting schemes in PHP re-query the MySQL database any time we want to change sort values for our data. And they traditionally flag the sort column and direction via a querystring variable.

First, we need to know how many columns our query will have. In the case of this demo, there are 5.

For security reasons, we don’t want to reveal actual column names in our table to end users; if they know the names of columns and values contained therein, it makes attacking our database via SQL injection a bit easier. (It’s also a bit cleaner to use integers vs. words, which will become manifest in a moment.) Instead of using column names, we will use index position, where 0 is the first column. So, since we have five columns in this demo, they are numbered 0-4.

Second, we need to know the direction of the sort. For this we will use values of 0 and 1, for ASC and DESC, respectively.

The first thing our script needs to do is see whether it has the column and direction $_GET variables set. If so, we need to translate the values into column name and direction variables; if not, we need to assign default values for each.

switch($_GET['c']) {
	case "1":
		$col = "person_name";
		break;
	case "2":
		$col = "person_surname";
		break;
	case "3":
		$col = "person_birthdate";
		break;
	case "4":
		$col = "person_department";
		break;
	default:
		$col = "person_id";
}

if($_GET['d'] == "1") {
	$dir = "DESC";
}
else {
	$dir = "ASC";
}

Notice that by creating default conditions, we are always certain of a valid value, even if someone tries to poison our $_GET variables; this also creates default sort conditions for the initial page call / when the $_GET variables have been excluded.

We’re now ready to connect to the database, get our records and output them to screen.

To accommodate the sorting, we’re going to create a special function called makeHeaderLink(). That function will take four arguments: The heading of the column, as we would like it to be publicly displayed; the actual name of the column in our table; the current value of $col, and the current value of $dir. (Note that we have to pass $col and $dir to the function because their scope is outside the ability of the function to reference their values, unless they are added as parameters.)

Based on these parameters, the function will return hyperlinked text providing a proper sorting link to the current page. We’ll use this function to create the header cells’ text in the output table.

function makeHeaderLink($value, $key, $col, $dir) {
	$out = "<a href=\"" . $_SERVER['SCRIPT_NAME'] . "?c=";
	//set column query string value
	switch($key) {
		case "person_name":
			$out .= "1";
			break;
		case "person_surname":
			$out .= "2";
			break;
		case "person_birthdate":
			$out .= "3";
			break;
		case "person_department":
			$out .= "4";
			break;
		default:
			$out .= "0";
	}

	$out .= "&d=";

	//reverse sort if the current column is clicked
	if($key == $col) {
		switch($dir) {
			case "ASC":
				$out .= "1";
				break;
			default:
				$out .= "0";
		}
	}
	else {
		//pass on current sort direction
		switch($dir) {
			case "ASC":
				$out .= "0";
				break;
			default:
				$out .= "1";
		}
	}

	//complete link
	$out .= "\">$value</a>";

	return $out;
}

And now, we can simply output the data to a table.

if(!$link = mysql_connect("server_name", "user_name", "password")) {
	echo "Cannot connect to db server";
}
elseif(!mysql_select_db("database_name")) {
	echo "Cannot select database";
}
else {
	if(!$rs = mysql_query("SELECT * FROM table ORDER BY $col $dir")) {
		echo "Cannot parse query";
	}
	elseif(mysql_num_rows($rs) == 0) {
		echo "No records found";
	}
	else {
		echo "<table class=\"bordered\" cellspacing=\"0\">\n";
		echo "<tr>";
		echo "<th>" . makeHeaderLink("Record ID", "person_id", $col, $dir) . "</th>";
		echo "<th>" . makeHeaderLink("First Name", "person_name", $col, $dir) . "</th>";
		echo "<th>" . makeHeaderLink("Last Name", "person_surname", $col, $dir) . "</th>";
		echo "<th>" . makeHeaderLink("Birthday", "person_birthdate", $col, $dir) . "</th>";
		echo "<th>" . makeHeaderLink("Department", "person_department", $col, $dir) . "</th>";
		echo "</tr>\n";
		while($row = mysql_fetch_array($rs)) {
			echo "<tr><td>$row[person_id]</td><td>$row[person_name]</td><td>$row[person_surname]</td><td>$row[person_birthdate]</td><td>$row[person_department]</td></tr>\n";
		}
		echo "</table><br />\n";
	}
}

You can see this in action here: http://demo.dougv.com/jquery_sort_records/

Needless to say, this is an exceedingly resource-intensive, and pretty much wasteful, manner of going about record sorting. It not only taxes the PHP server, it taxes the MySQL server, too. And since we’re not changing anything, in terms of the records we expect back, it’s especially wasteful; we really only need to get the records once, but the technology won’t let us.

There are a few ways in PHP in which we could reduce the server load.

One option would be to use two pages: One page that actually calls to the database to get records, store them somehow — in, say, an XML file, or a serialized session object, or even just an array stored to a session — then automatically pass that new-stored information to a second page, which in turn handles sorting.

Even so, that’s a lot of work — for us, as well as the servers involved — to perform a relatively simple task. Were we only able to somehow pass on to the client the records and let it do the sorting. …

The Better Approach: jQuery

And there’s good news: We effectively do just that passing on of records to the client when we create the results table. After all, an HTML table is a bunch of data organized into columns and rows; so, ultimately, it’s a data store. And thanks to the Document Object Model, we can treat any HTML table as such.

We could go through the hard work of writing JavaScript to turn each row of our output table into an index of a JavaScript array (or a JSON object), then use the built-in sort() method to arrange them as we want.

But the news keeps getting better: Christian Bach has beat us to the punch with tablesorter, a jQuery plug in that, as its name suggests, allows us to simply call his library in order to get full table sorting instantly.

So, our first task is to download jQuery and tablesorter, upload them to our Web site, and add references to them on our page.

<script type="text/javascript" src="jquery-1.3.1.min.js"></script>
<script type="text/javascript" src="jquery.tablesorter.min.js"></script>

Next, we output the records exactly as we did in the PHP-only example, with five changes:

  • We give the table a unique ID; in this case, “sortedtable,” but you can use any legal name you like.
  • We don’t bother with the PHP-based sorting links or any of the PHP sorting logic; we don’t need it.
  • We add thead tags to the table’s header rows, so that the tablesorter library knows which cells we want to have be clickable for sorting purposes.
  • We add CSS for the th tags on our table, to make them look like links; the tablesorter library won’t change the style of the header rows, and we want to make it obvious they are clickable sorting links.
  • We invoke the tablesorter library on the table via jQuery’s $(document).ready() method.

Let’s start with the CSS we’re adding:

#sortedtable thead th {
	color: #00f;
	font-weight: bold;
	text-decoration: underline;
}

Next, the JavaScript that will invoke tablesorter:

$(document).ready(function() {
	$("#sortedtable").tablesorter({ sortlist: [0,0] });
});

And now, for the modified PHP code that will create our table:

if(!$link = mysql_connect("server_name", "user_name", "password")) {
	echo "Cannot connect to db server";
}
elseif(!mysql_select_db("database_name")) {
	echo "Cannot select database";
}
else {
	if(!$rs = mysql_query("SELECT * FROM table")) {
		echo "Cannot parse query";
	}
	elseif(mysql_num_rows($rs) == 0) {
		echo "No records found";
	}
	else {
		echo "<table id=\"sortedtable\" class=\"bordered\" cellspacing=\"0\">\n";
		echo "<thead>\n<tr>";
		echo "<th>Record ID</th>";
		echo "<th>First Name</th>";
		echo "<th>Last Name</th>";
		echo "<th>Birthday</th>";
		echo "<th>Department</th>";
		echo "</tr>\n</thead>\n";
		while($row = mysql_fetch_array($rs)) {
			echo "<tr><td>$row[person_id]</td><td>$row[person_name]</td><td>$row[person_surname]</td><td>$row[person_birthdate]</td><td>$row[person_department]</td></tr>\n";
		}
		echo "</table><br />\n";
	}
}

And with that, we have a sortable table that works entirely client-side. Working example: http://demo.dougv.com/jquery_sort_records/js.php

Source code on github: https://github.com/dougvdotcom/jpquery_php_sort_table

I distribute code under the GNU GPL. See Copyright & Attribution for details.

All links in this post on delicious: http://delicious.com/dhvrm/sorting-your-results-set-in-php-mysql-using-jquery-and-a-more-traditional-approach

36 Comments

  1. hey, thanks a lot dougv for sharing..
    I was just browsing around and landed in this page…

    well, I haven’t try the codes yet, and I actually got a question..
    last time i did try something similar, but using another methods (javascript *ithink*), and it took ages to sort when there are more than 1000 rows..
    In your sample, there are “11 Record IDs”, what if there are more than 1000, will it be able to sort them out properly?

    thanks for the answers :))

  2. @lizzy: You should consider paginating your results set if you intend to sort thousands of records.

    From the standpoint of PHP, provided your data has a primary key, which column you sort on really shouldn’t affect MySQL query performance. You may find the jQuery solution does not perform well in older Web browsers when sorting so many records.

    JavaScript sorting performance is a factor of the browser being used and the client computer’s power, so answering how effective a sort is for an individual client is hard to say. It is probably fair to say that sorting thousands of table cells at one time via the DOM is not advisable.

    If you really want to ship the workload for working with records onto the client, you should consider using Google Gears and its local database feature.

  3. This would only order whats already been loaded.

    I still want to use ORDER BY because I want to sort the whole table, not just whats on screen. I’ve used LIMIT to show only 20 records out of thousands, if I use tablesrter it will only sort the current 20 records and not the whole database.

    The traditional way you have described would work for this but then I have to wait for the page to reload, how can I use jquery so it sorts without having to reload the page?

  4. @doug: thanks for your help. Seems like a possible solution for a small database, but mine has over 1 million records.

    I was thinking of performing the sort using ORDER BY. But maybe using jqueries load() thing so that the page doesn’t refresh.

    I’m a total noob to jquery, so I don’t really know how I’d go about doing that.

  5. Hi – very clear instructions – worked a treat for me in one osCommerce site – found a problem with getting it to run in an iframe – it didn’t matter where I inserted the include for the jquery files it looked as though the browser was unable to locate the js file (according to firebug) – any clues?

  6. @Graeme: I’m glad this works for you. Almost certainly your issue is related to jQuery not being able to properly identify the container in which the table you want to sort appears. When it comes to specific implementations of the code I offer here, I do provide support in exchange for a Wish list purchase. Check out the details under “Need more help or want to say thanks,” above. Thanks!

  7. Hi Doug, would you be willing to share a sample file? I’m trying to get this to work, but the column headers aren’t clickable, and I’m quite lost here (still a PHP n00b).. Thanks!

  8. Is there any way to put a row above the row that will have the links for sorting? The makes that row automatically be at the top but if I leave the out then the sorting does not work. Thanks!

  9. Yay.. thank you Doug for the code. Especially the PHP one. I understand that it heavily hurts the server but this way make me understand the world easier than just two rows of jQuery 🙂
    I have another question: is it possible to paginate the html output by jQuery? Currently I do this purely by PHP 🙂
    gaixixon.

  10. I have some more questions: AFAIK, the php output *all* the pages, and the jquery hide some pages, and show only one by one. What happen if i have say 100pages? Each time user visit the site, php output all the 100pages, and jquery actually show each page but the server still have to output the same information!
    I hope you get my point.

  11. @gaixixon: tablesorter has a pagination plugin, available at the same link. Its drawback is that it will only allow you to sort what is on the screen, not the entire recordset.

    Thus, you should go the PHP route and use LIMIT. So, for example, if you have 50 records per page, and you want to show page 15, you would need to calculate, and add to both the header and a new, page navigation footer row, the page you want to use, like this:

    define('MYSQL_PAGE_SIZE', 50);
    
    $curpage = $_GET['p'];
    
    if(!isset($curpage) || !preg_match('/[0-9]{1,3}/', $curpage])) {
    	$curpage = 1;
    }
    
    $minrecord = ($curpage - 1) * MYSQL_PAGE_SIZE;
    
    $sql .= " LIMIT $minrecord, MYSQL_PAGE_SIZE";
    
  12. Hi Doug,

    Thanks for tutorial it looks great 🙂

    I get “cannot parse query”. Isit something im doing wrong in the mysql query?

    if(!$rs = mysql_query(“SELECT Acronym, Full Name, What * FROM jargon1 ORDER BY $col $dir”)) {

  13. I got it working 😀

    if(!$rs = mysql_query(“SELECT `Acronym`, `Full Name`, `What` FROM `jargon1` ORDER BY $col $dir”))

    Thanks man 😀

  14. Hi there, if you use the pagination option with tablesorter and assuming records can be viewed by clicking on it, can you go back to the same page the record was viewed on? i.e: I click on a record on page 4 of 10, when I go back it takes me to page 1 instead of page 4 – how would you go about fixing this? thanks.

  15. @greenpool: The short answer is, tablesorter’s pagination plugin does not maintain state. Therefore, you can’t go across pages. Solution? Not to be glib, but the only solution I can think of is to write something that does maintain state.

  16. @simple: Yes, one could create a JSON object or XML table or array and go about making it sortable that way. But that’s pointless. Most tabular data presented on the web is contained in an external store (e.g., database) and needs to be retrieved through a server-side process anyway. And as I’ve noted repeatedly, a developer should not rely on a client having JavaScript enabled. That’s especially the case here. If tabular data must be sortable, postbacks are the way to go, even when wasteful. If it’s just a great feature, jQuery can do the job.

  17. Hi Doug,
    Thank you very much for this very easy to implement solution and certainly a big thanks to Christian Bach too.
    Quick question, all my columns are sorting except the date column, data is stored in MySQL as ‘date’ (not varchar or anything else) and I am using date(‘d-M-Y’, strtotime($mydate)) to display the date as for e.g., 27-Jun-2014 but this is the only column tablesorter seems to be unable to sort.
    I have tried forcing various sorters such as isoDate, usLongDate and shortDate but to no avail.
    Your thoughts please?

  18. @Xeirus: You can write custom parsers for tablesorter. Doing this is not too complicated, but I can’t get into detail in the confined space of the comments section.

    In your case, you’d make a parser that would cast the displayed date back to a microtimestamp, then sort those values numerically. Something like this:

    $.tablesorter.addParser({ 
    	// set a unique id 
    	id: 'mydatesortmethod', 
    	is: function(s) { 
    		// return false so this parser is not auto detected 
    		return false; 
    	}, 
    	format: function(s) { 
    		// format your data for normalization 
    		var tmp = new Date(s);
    		return tmp.getTime(); 
    	}, 
    	// set type, either numeric or text 
    	type: 'numeric' 
    }); 
    

    And call it like this, if the date column was the first in the table with the ID mytable:

    $('#mytable').tablesorter({ 
    	headers: { 
    		0: { 
    			sorter:'mydatesortmethod' 
    		} 
    	} 
    });
    
  19. Jquery sort is usefull when you have one page results. But if you have multiple pages (using pagination) then it is useless…as you must click on sorting buttons everytime you load new page…

  20. This error occurs. I don’t know why. I just copied the codes and replace and add cases in switch.

    Notice: Undefined index: c in C:\xampp\htdocs\PHP\MIS\admin\admin.php on line 170

    switch($_GET['c']) {

    Notice: Undefined index: d in C:\xampp\htdocs\PHP\MIS\admin\admin.php on line 221

    if($_GET['d'] == "1") {

    can anyone please help me? Thanks in advance

  21. @Maine: The error means you are not sending in querystring variables with the keys of c and d. Or you have changed the code in some other way that requires querystring variables of c and d to be present.

    In other words, your request URL needs to look something like: http://www.example.com/admin/admin.php?c=0&d=1

    So the script is fine; you’re just not properly calling it.

    If this continues to be a problem, simply add an isset test to both expressions:

    if(isset($_GET['c']) { switch($_GET['c']) {
    if(isset($_GET['d'] && $_GET['d'] == "1") {

Leave a Reply

  • Check out the Commenting Guidelines before commenting, please!
  • Want to share code? Please put it into a GitHub Gist, CodePen or pastebin and link to that in your comment.
  • Just have a line or two of markup? Wrap them in an appropriate SyntaxHighlighter Evolved shortcode for your programming language, please!