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

We Love InnerHTML! - 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

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

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

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



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