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.

{mospagebreak title=The Test Scenario}

First, we’ll create a products table in our MySQL test database and insert some records on it:

CREATE TABLE products (

  ID smallint(5) unsigned NOT NULL auto_increment,

  NAME varchar(125) NOT NULL default ”,

  PRICE float(8,2) NOT NULL default ‘0.00’,

  CODE varchar(10) NOT NULL default ”,

  WEIGHT smallint(5) unsigned NOT NULL default ‘0’,

  PRIMARY KEY (ID)

  ) TYPE=MyISAM;


INSERT INTO products VALUES (1, ‘Absolute Delight’, ‘2.20’, ’59’, 50);

  INSERT INTO products VALUES (2, ‘Aqua Mirabilis’, ‘2.35’, ‘114’, 15);

  INSERT INTO products VALUES (3, ‘Back For Breakfast’, ‘8.30’, ‘1409’, 500);

  INSERT INTO products VALUES (4, ‘Breeze on a Sea Air’, ‘5.95’, ‘223’, 75);

  INSERT INTO products VALUES (5, ‘Figs and Leaves’, ‘3.90’, ‘22819’, 100);

  INSERT INTO products VALUES (6, ‘I love Juicy’, ‘5.95’, ‘2006’, 250);

  INSERT INTO products VALUES (7, ‘Ocean Salt’, ‘9.75’, ‘2128’, 150);

  INSERT INTO products VALUES (8, ‘Serenity’, ‘49.95’, ‘2083’, 350);

  INSERT INTO products VALUES (9, ‘Tisty Tosty’, ‘2.35’, ‘5’, 60);

  INSERT INTO products VALUES (10, ‘Waving Not Drowning’, ‘1.95’, ’15’, 40);

If nothing went wrong we should have our products table with 10 records. We are now ready to jump into the actual code!

{mospagebreak title=The Basic Code}

Let’s write the basic code: it will retrieve the products from the database and display them in a table.

<?

//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>';

while ($a=mysql_fetch_row($res))
{
 //Append the table row to our $output variable
 $output.=”<tr id=’line$a[0]‘><td>$a[0]</td><td>$a[1]
</td><td>$a[2]</td><td>$a[3]</td><td>$a[4]</td></tr>”;
}

$output.='</table>';

?>

<html>
<head>
</head>

<body>

<div id=’content’>

<?=$output?>

</div>

</body>
</html>

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


Ordering Columns in DDBB Search Results


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):

<tr id=’line1′><td>1</td><td>Absolute
Delight</td><td>2.20</td><td>59</td><td>50</td></tr>

<tr id=’line2′><td>2</td><td>Aqua
Mirabilis</td><td>2.35</td><td>114</td><td>15</td></tr>

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);

while ($a=mysql_fetch_row($res))
{
 //Append the table row to our $output variable
 $output.=”<tr id=’line$a[0]‘><td>$a[0]</td><td>$a[1]
</td><td>$a[2]</td><td>$a[3]</td><td>$a[4]</td></tr>”;

 //Add this product to the prices array
 $prices[$a[0]]=$a[2];
}

$output.='</table>';

//We now do the sorting of the prices array, and store
//it in a JS array
$jsOutput=’var ordPrice=new Array(‘;

asort($prices, SORT_NUMERIC);
reset ($prices);

while (list ($key, $val) = each ($prices))
{
 $jsOutput.= “$key,”;
}

$jsOutput=substr($jsOutput,0,-1);
$jsOutput.=”);rn”;

//And we reverse the array to generate the inverse
//order JS array
$jsOutput.=’var ordPriceInv=new Array(‘;

$prices=array_reverse($prices, TRUE);
reset ($prices);

while (list ($key, $val) = each ($prices))
{
 $jsOutput.= “$key,”;
}

$jsOutput=substr($jsOutput,0,-1);
$jsOutput.=”);rn”;
?>

<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
 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>

</head>
<body>

<div id=’content’>

<?=$output?>

</div>

<br><br> <input type=”button” onClick=”order
(‘ordPrice’)” value=”Order products by price”>

<br><br> <input type=”button” onClick=”order
(‘ordPriceInv’)” value=”Order products by price
(inverse)”>

</body>
</html>

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:

var ordPrice=new Array(10,1,9,2,5,4,6,3,7,8);
var ordPriceInv=new Array(8,7,3,6,4,5,2,9,1,10);

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:

<?

//The function that sorts any array and generates the
//JS output
function gen_ord_array($name,$variable, $flag)
{
 $out=”var $name=new Array(“;
 asort($variable, $flag);
 reset ($variable);
 while (list ($key, $val) = each ($variable))
  $out.= “$key,”;
 $out=substr($out,0,-1);
 $out.=”);rn”;
 
 //Now for the inverse JS array
 $out.=”var $name”.”Inv=new Array(“;
 $variable=array_reverse($variable, TRUE);
 reset ($variable);
 while (list ($key, $val) = each ($variable))
  $out.= “$key,”;
 $out=substr($out,0,-1);
 $out.=”);rn”;

 return $out;
}

//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
//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

$output_h='<table border=”1″ cellpadding=”5″><tr
style=”font-weight:bold”>'; $output_h.='<td>ID</td>';

$output_h.='<td><a href=”#” onClick=”order
(‘ordName’)”>v</a> &nbsp; NAME &nbsp; <a href=”#”
onClick=”order(‘ordNameInv’)”>^</a></td>';

$output_h.='<td><a href=”#” onClick=”order
(‘ordPrice’)”>v</a> &nbsp; PRICE &nbsp; <a href=”#”
onClick=”order(‘ordPriceInv’)”>^</a></td>';

$output_h.='<td><a href=”#” onClick=”order
(‘ordCode’)”>v</a> &nbsp; CODE &nbsp; <a href=”#”
onClick=”order(‘ordCodeInv’)”>^</a></td>';

$output_h.='<td><a href=”#” onClick=”order
(‘ordWeight’)”>v</a>&nbsp; WEIGHT &nbsp; <a href=”#”
onClick=”order(‘ordWeightInv’)”>^</a></td>';

$output_h.='</tr>';

$output=$output_h;

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

while ($a=mysql_fetch_row($res))
{
 //Append the table row to our $output variable
 $output.=”<tr id=’line$a[0]‘><td>$a[0]</td><td>$a[1]
</td><td>$a[2]</td><td>$a[3]</td><td>$a[4]</td></tr>”;
 //Add this product to the sort arrays
 $names[$a[0]]=$a[1];
 $prices[$a[0]]=$a[2];
 $codes[$a[0]]=$a[3];
 $weights[$a[0]]=$a[4];
}

$output.='</table>';

//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+"];”);

  eval (“out+=”<tr id=’line”+row_number+”‘>”+document.getElementById
(‘line”+row_number+”‘).innerHTML+'</tr>';”);
 }
 out+='</table>';

 //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!

There is an online sample in http://www.karontek.com/koas/order.php

Have fun, and happy coding!!!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan