Plz help: Dynamic on click table header sorting - desc/asc

Hello folks! I have been trying to follow a tutorial online that teaches a dynamic way of sorting MySQL result sets in an HTML table based on its column headings being sort links. I have my code working but it only sorts by the column heading ascending once and not in descending order when you click on it a second time. :xeye:

The tutorial itself kind of leaves you hanging and does not teach you how to finish the dynamic sorting method. I am hoping if I post my code that someone can help me finish the code to produce sorting the results both ascending and descending depending on the users clicking habits of the column header link (by default its ascending, then if a user clicks the column header it re-sorts descending, then if clicked again, it sorts ascending again…and so on).

I have incorporated alternating table row colors into this code as well so this may help someone else. I am posting my database schema too to help others with similar questions.

I figure some kind of if-else, ternary operator, or switch statement must be used but I am lost as to how to code it. I would appreciate any help you can offer and please be brutally specific in your replies as I am a newbie to some of the PHP/MySQL coding (still working on Kevin’s book as we speak!) Thank you in advance!!! :smiley:

 
CREATE DATABASE AltRowColor;
USE AltRowColor;
CREATE TABLE AltRowColorData (
ID int(15) NOT NULL AUTO_INCREMENT,
SomeText varchar(15) NOT NULL,
SomeDate DATE NOT NULL,
PRIMARY KEY (ID)
) TYPE=MyISAM; 
INSERT INTO AltRowColorData VALUES (1, "Row 1", "2003-04-01");
INSERT INTO AltRowColorData VALUES (2, "Row 2", "2003-04-02");
INSERT INTO AltRowColorData VALUES (3, "Row 3", "2003-04-03");
INSERT INTO AltRowColorData VALUES (4, "Row 4", "2003-04-04");
INSERT INTO AltRowColorData VALUES (5, "Row 5", "2003-04-05");
INSERT INTO AltRowColorData VALUES (6, "Row 6", "2003-04-06");
INSERT INTO AltRowColorData VALUES (7, "Row 7", "2003-04-07");
INSERT INTO AltRowColorData VALUES (8, "Row 8", "2003-04-08");
INSERT INTO AltRowColorData VALUES (9, "Row 9", "2003-04-09");
INSERT INTO AltRowColorData VALUES (10, "Row 10", "2003-04-10");

My PHP code to date:


<?PHP
/* set the allowed order by columns */
$default_sort = 'ID';
$allowed_order = array ('ID','SomeText', 'SomeDate');
/* if order is not set, or it is not in the allowed
* list, then set it to a default value. Otherwise, 
* set it to what was passed in. */
if (!isset ($_GET['order']) || 
	!in_array ($_GET['order'], $allowed_order)) {
	$order = $default_sort;
} else {
	$order = $_GET['order'];
}
/* connect to db */
@mysql_connect ('localhost','****','******'); //username and pass are blanked here, fill in for your own server setup

@mysql_select_db ('AltRowColor');
/* construct and run our query */
$query = "SELECT ID,SomeText, SomeDate FROM AltRowColorData ORDER BY $order";
$result = @mysql_query ($query);
/* make sure data was retrieved */
$numrows = @mysql_num_rows($result);
if ($numrows == 0) {
	echo "No data to display!";
	exit;
}
/* now grab the first row and start the table */
$row = mysql_fetch_assoc ($result);
echo "<TABLE border=0>\
";
echo "<TR bgcolor=\\"lightblue\\">\
";
foreach ($row as $heading=>$column) {
	/* check if the heading is in our allowed_order
	 * array. If it is, hyperlink it so that we can
	 * order by this column */
	echo "<TD><b>";
	if (in_array ($heading, $allowed_order)) {
		echo "<a href=""\\"{$_SERVER['PHP_SELF']}?order=$heading\\">$heading</a>";
	} else {
		echo $heading;
	}				
	echo "</b></TD>\
";
}
echo "</TR>\
";
/* reset the $result set back to the first row and 
* display the data */
// Define your colors for the alternating table rows
$color1 = "#CCFFCC";
$color2 = "#BFD8BC";
$row_count = 0;
mysql_data_seek ($result, 0);
while ($row = mysql_fetch_assoc ($result)) {
$row_color = ($row_count % 2) ? $color1 : $color2;
echo "<TR bgcolor=\\"$row_color\\">\
";
	foreach ($row as $column) {
		echo "<TD>$column</TD>\
";
	}
	echo "</TR>\
";
	$row_count++;
}
echo "</TABLE>\
";
?>  
 

Great.

What you do is have use another variable from get to change between asc and desc.

When you are creating the links check if that collumn is sorted, if it is sorted you need to reverse the order.


 <?PHP
 /* set the allowed order by columns */
 $default_sort = 'ID';
 $allowed_order = array ('ID','SomeText', 'SomeDate');
 /* if order is not set, or it is not in the allowed
 * list, then set it to a default value. Otherwise,
 * set it to what was passed in. */
 if (!isset ($_GET['order']) ||
 	!in_array ($_GET['order'], $allowed_order)) {
 	$order = $default_sort;
 } else {
 	$order = $_GET['order'];
 }
 //Check if you need to reverse the order.
 if(isset($_GET['desc']))
 {
 	$order .= ' DESC';
 }
 /* connect to db */
 @mysql_connect ('localhost'); //username and pass are blanked here, fill in for your own server setup
 
 @mysql_select_db ('AltRowColor');
 /* construct and run our query */
 $query = "SELECT ID,SomeText, SomeDate FROM AltRowColorData ORDER BY $order";
 $result = @mysql_query ($query);
 /* make sure data was retrieved */
 $numrows = @mysql_num_rows($result);
 if ($numrows == 0) {
 	echo "No data to display!";
 	exit;
 }
 /* now grab the first row and start the table */
 $row = mysql_fetch_assoc ($result);
 echo "<TABLE border=0>\
";
 echo "<TR bgcolor=\\"lightblue\\">\
";
 foreach ($row as $heading=>$column) {
 	/* check if the heading is in our allowed_order
 	 * array. If it is, hyperlink it so that we can
 	 * order by this column */
 	echo "<TD><b>";
 	if (in_array ($heading, $allowed_order)) {
 		if($heading == $order) //Already chosen asc so link to desc
 		{
 		    printf('<a href="%s">%s</a>',"{$_SERVER['PHP_SELF']}?order=$heading&desc=1",$heading);
 		}
 		else
 		{
 		printf('<a href="%s">%s</a>',"{$_SERVER['PHP_SELF']}?order=$heading",$heading);
 		}
 	} else {
 		echo $heading;
 	}				
 	echo "</b></TD>\
";
 }
 echo "</TR>\
";
 /* reset the $result set back to the first row and
 * display the data */
 // Define your colors for the alternating table rows
 $color1 = "#CCFFCC";
 $color2 = "#BFD8BC";
 $row_count = 0;
 mysql_data_seek ($result, 0);
 while ($row = mysql_fetch_assoc ($result)) {
 $row_color = ($row_count % 2) ? $color1 : $color2;
 echo "<TR bgcolor=\\"$row_color\\">\
";
 	foreach ($row as $column) {
 		echo "<TD>$column</TD>\
";
 	}
 	echo "</TR>\
";
 	$row_count++;
 }
 echo "</TABLE>\
";
 ?> 
 

Hey Vex, Thanks very much for your reply! I have not tried out your code addition yet because my laptop is getting repaired at the moment, but when she comes back I will definitely try this out. Thanks for taking the time to reply to this thread! I was beginning to think that this topic was invisible!

Sincerely,

Drew F.

Vex, I wanted to thank you for your code input. Your suggestion works wonderfully! One simple line…how did I miss that? I tell ya, having a fresh set of eyes look at things makes all the difference in the world.

Now I am going to try and build in some pagination and hopefully I don’t get lost in the individual page offset…we’ll see how that all goes. But for now you solved my dilema wonderfully. Thank you very much for responding!

My 2 cents: at brainjar you can find a javascript solution for client-side table sorting. Kinda amazing.

Had to change the sorting algorithm to quicksort, though.

That Javascript stuff is pretty cool actually, not sure how to implement all that in with MySQL though off the top of my head but I am sure it can be done. Thanks for posting that page Ele1138! Good stuff to add to the JS library.