dougv.com « Doug Vanderweide

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://www.dougv.com/demo/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://www.dougv.com/demo/ajax_sort_records/js.php

You can download the code for this project here: Sorting Your Results Set In PHP / MySQL Using jQuery (And A More Traditional Approach) Demo Code

I distribute all code under the GNU GPL version 3.

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