Login

Ordering Columns in DDBB Search Results

This article will show you the fastest way to order your database results,
via JavaScript (with some work in PHP). It adds some more work to your
script, but once you see the results you won’t use the approach of adding the
ORDER By clause anymore.

Introduction

I bet most of us have at some point developed an application where a list of products is retrieved from a database and shown in a table with its name, price and code. Perhaps some of us thought: “Hey, let’s be nice and give the user the chance to order these results by name or price.” So we placed a cute little icon near each column’s header that linked to the search page with a flag in the URL to let it know we wanted the results ordered by name or price. The search script kindly did its job, adding the ORDER BY clause, and the ordered results were shown to the user.

Obviously this works, but there is a problem: you must search again in your database, and send the user the whole page with the new results.

So let’s think — the first time you load the page, the results are already in the user browser. Why must we bother the server again to retrieve the same results in a different order?

This article will show you the fastest way to order your database results, via JavaScript (with some work in PHP). It adds some more work to your script, but once you see the results you won’t use the approach mentioned above anymore.

Okay, this piece of code should produce this output, a table with all the products on it:

Make sure you check the while loop. We assign each TR element an id, being this id the ID number of the product (the ID number of each product is unique, so we are sure there won’t be two TR with the same ID):

This table is then echoed into a DIV element, which I’ve assigned the id “content”.

I can hear you saying “Yes I know where you want to go!” Not really? Ok, flip the page and let’s get our hands dirty with the sorting!

{mospagebreak title=We Love InnerHTML!}

Right now we have a table with its rows numbered. Each row’s number is the ID of the product it contains. The idea of the fast sort is simple: to sort the table by, let’s say, product’s price we just need to know the order of the IDs when the products list is ordered by price. So we’ll sort the products list by price with PHP’s array functions, and pass the IDs to a JavaScript array, which will be used to reorder the rows of the table.

Hmm… I think it’ll be easier with an example:

<? //Connect to the database$conn=mysql_connect(‘localhost’,’root’,”) or die (‘Sorry, no connection to database available :-(‘);

//Perform our query$query=’SELECT * FROM test.products’;

if (!$res=mysql_query($query, $conn)){ die (‘Sorry, query error’);}

//We populate the $output variable with the HTML code to generate the results table$output='<table><tr style=”font-weight:bold”><td>ID</td><td>NAME</td><td>PRICE</td><td>

CODE</td><td>WEIGHT</td></tr>’;

//We keep the number of results of the query, they will be used in JavaScript$num_results=mysql_num_rows($res);

function order (field){ //This var will store the row number we are grabbing // from the table var row_number=0;

//We must generate again the table headers var out='<table><tr style=”font-weight:bold”><td>ID</td><td>NAME</td><td>PRICE</td><td>CODE</td><td>WEIGHT</td></tr>’;

//The field variable is the name of the array we want //to use to reorder our table //We travel across it and grab the content of the row //that has that ID, and append it to our out variable for (x=0;x<num_results;x++) { eval (“row_number=”+field+”[“+x+”];”); eval (“out+=”<tr id=’line”+row_number+”‘>”+document.getElementById(‘line”+row_number+”‘).innerHTML+'</tr>’;”); } out+='</table>’; //Finally, we set the innerHTML of the content div to //our new table document.getElementById(‘content’).innerHTML=out;}</script>

Let’s see what we’ve done. We have this PHP array $prices that holds the prices of the products, with its keys being the product’s ID and its values the price of the product. We sort it numerically, and store its (now ordered by price) indexes in a JS array. If we reverse it, we can store its reverse ordered indexes in another JS array. This is what the JS arrays look like:

Then we have the JS order function, which accepts one parameter called field. This parameter is the name of the JS array we’ll use to reorder the rows of our table. The function obtains the ID of the first product (following the new order), grabs the contents of its row and appends them to the output variable. Notice that since the innerHTML property stores only the contents of the TR tags, we must append manually the TR tags to the output variable, and that means setting the id too.

Once we have the HTML code of the new table, we just send it to the innerHTML property of the content object, which displays it immediately. Now, tell me, don’t you love innerHTML? I personally think it’s the best thing since sliced bread!

{mospagebreak title=I Need More!}

Yes! Once you see how fast this code sorts rows in a table you just have to use it with the other columns. So, let’s see the final example, a little bit more optimized to make your life easier:

$conn=mysql_connect(‘localhost’,’root’,”) or die (‘Sorry, no connection to database available :-(‘);

//Perform our query$query=’SELECT * FROM test.products’;

if (!$res=mysql_query($query, $conn)){ die (‘Sorry, query error’);}

//We populate the $output variable with the HTML code//to generate the results table//Now the headers have links to perform the sorting//We’ll keep the table header in another variable to //save us time in the JS function

//We now do the sorting of the arrays, and store them //in a JS array$jsOutput=gen_ord_array(‘ordName’,$names,SORT_STRING);$jsOutput.=gen_ord_array(‘ordPrice’,$prices,SORT_NUMERIC);$jsOutput.=gen_ord_array(‘ordCode’,$codes,SORT_STRING);$jsOutput.=gen_ord_array(‘ordWeight’,$weights,SORT_NUMERIC);?>

<html>

<head>

<script language=”JavaScript”>

var num_results=<?=$num_results?>;

//We output the array definitions<?=$jsOutput?>

//This is the function that performs the actual//sorting

function order (field){ //This var will store the row number we are grabbing //from the table var row_number=0;

//We must generate again the table headers //Luckily, we already have them in the PHP $output_h variable //But we must escape the single quotes!! var out='<?=str_replace(“‘”,”\'”,$output_h)?>’;

//The field variable is the name of the array we want to use to reorder our table// We travel across it and grab the content of the row// that has that ID, and append it to our out variable for (x=0;x<num_results;x++) { eval (“row_number=”+field+”[“+x+”];”);

//Finally, we set the content of the content div to //our new table document.getElementById(‘content’).innerHTML=out;}</script>

</head> <body>

<div id=’content’>

<?=$output?>

</div>

</body> </html>

We’ve written a PHP function that will take care of sorting the PHP arrays and generating the JS arrays. Its first parameter is the name of the array (this is the parameter we pass to the JS order function), the second is the array of data to sort, and the third a flag that indicates how the function will sort the array.

The table headers are kept in a variable so we don’t have to write them again in the JS order() function, and links are added to the left and right of each column’s header, links that call the JS function order() with the name of the array we want to use for the sorting.

{mospagebreak title=The Final Words}

In this article we’ve explored a nice example of how to sort the results of a database search in real time, giving the user a fast response which he or she will surely appreciate. It’s not a lot of extra work to do, and I think it’s really worth it.

By the way, I like the idea of PHP generating JS that generates HTML!!

Of course, there is a major problem with this trick. If the user browser is too old and doesn’t know what innerHTML is, we’re in trouble. This trick should only be used if you detect that the user’s browser can handle the innerHTML property. If it can’t, the “older” approach I explained on the first page of the article could be used, maybe with a message to the user: “Why do you think software companies develop newer versions of their browsers?”

This code was tested succesfully in a P4 1’8 GHz 256 Mb RAM with Windows 2000 Professional, Apache/1.3.27, MySQL 4.0.12, PHP 4.3.1 with the following browsers: Mozilla 1.4 and IE 6.0. It may not be 100% bug free, so I’d appreciate any feedback!