Home arrow PHP arrow Page 5 - Ordering Columns in DDBB Search Results

I Need More! - PHP

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.

TABLE OF CONTENTS:
  1. Ordering Columns in DDBB Search Results
  2. The Test Scenario
  3. The Basic Code
  4. We Love InnerHTML!
  5. I Need More!
  6. The Final Words
By: Alvaro Calleja
Rating: starstarstarstarstar / 13
December 06, 2004

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.=");\r\n";
 
 //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.=");\r\n";

 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.



 
 
>>> More PHP Articles          >>> More By Alvaro Calleja
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: