JAVASCRIPT

Sorting the Easy Way

I bet most of us have developed an application where a list of products were retrieved from a database and shown in a table with their name, price and code. 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 method works, but there is a problem: Why must we bother the server again to retrieve the same results that are now held in the browser, only in a different order? This article will show you the quickest way to order your database results, via JavaScript, with some work in PHP.

Author Info:

By: Alf A. Pedersen

Poor Best

Rating:

/ 9

March 17, 2004

TABLE OF CONTENTS:

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

<?php //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+"').inner HTML+'</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>