Sorting table columns ascending and descending

Hi, I have this table generated with values from the database. By default, the data will be sorted ascendingly according to first column. I want the data to be sorted according to any column, that would mean whenever I click on the title of any column, it would sort descendingly, then if I click at the same column again, it will revert back to ascending sort, and so on.

Well, I can make them work only once, i.e. descendingly, after that I can't revert the sorting back to ascending. Can anyone help me on this? I hope I don't have to use any JavaScript on this...

Thanks.



<?PHP
// Get the name of the column to sort
$columnname = "";
if ($_GET['columnname']) {
$columnname = $_GET['columnname'];
}

// Get the value of either ascending or descending
$order = "";
if ($_GET['order']) {
$order = $_GET['order'];
}

// Display 20 records per page.
$display_number = 20;

// Connect to the database.
$db_connection = mysql_connect ('localhost', 'root', '123456') or die (mysql_error());
$db_select = mysql_select_db('proposal');

// Make the calculation to see how many pages are there.
if (!isset($num_pages)) {

$query1 = "SELECT * FROM proposals";

// Query the database.
$query_result1 = mysql_query ($query1) or die (mysql_error());

// Calculate the number of pages required.
$num_results = @mysql_num_rows ($query_result1);

if ($num_results > $display_number) {

$num_pages = ceil ($num_results/$display_number);

} elseif ($num_results > 0) {

$num_pages = 1;

} else {

echo '<font color="#006699" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>There are no proposals at all.</b></font>';

}

// Currently at item 0.
$start = 0;

}

// Make the default column name
if ($columnname == "")
{$columnname = "proposalno"; }

// Make the default order sorting
if ($order == "") {
$order = "ASC"; }
elseif ($order == "ASC") {
$order = "DESC";}
elseif ($order == "DESC") {
$order = "ASC";}

// Make the new, limited query.
$query = "SELECT * FROM proposals ORDER BY $columnname $order LIMIT $start, $display_number";

// Print a table.
echo '<br><table width="760" align="center" cellpadding="0" cellspacing="0" border="1" bordercolor="#3399CC">
<tr align="center" bgcolor="#006699">

<td width="100">
<div align="center">
<font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Proposal No</b></font>
</div>
</td>

// Here is the column that I tested...
<td width="260">
<div align="center">
<font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b><a href="proposal-basicview.php?columnname=clientsname&order=ASC">Client Name</a></b></font>
</div>
</td>

<td width="400">
<div align="center">
<font color="#FFFFFF" size="2" face="Verdana, Arial, Helvetica, sans-serif"><b>Project Title</b></font>
</div>
</td>

</tr>';

// Print each item.

$query_result = @mysql_query ($query);

while ($row = @mysql_fetch_array ($query_result)) {

echo " <tr align=\"center\" bgcolor=\"$row[colourcode]\">
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[proposalno]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[clientsname]</font></td>
<td align=\"left\"><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\" color=\"#006699\">&nbsp$row[projecttitle]</font></td>
</tr>";

}
...
?>

If you want to do this in PHP, then you need to post the page to itself, with the sorting order in the querystring. Like
www.thesite.com/thepage.php?sort=asc
Then inside the page, you grab the querystringvalue and inject it in the SQL statement.
Like

$order="asc"; // the defaultorder
$order=$_GET['sort'] // default will be replaced if there was a value in the querystring
...

Then you display a link to sort them differently. Like

if ($order=="asc") {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
} else {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;
}
echo $orderlink ;


But i see you use a limit clause, so you will get different records, cause the records are first sorted, and then the limit-clause is executed to build the recordset ...

Hi Raf, thanks for guidance. It seems what you showed here is almost the same concept as mine, i.e. post to the page itself and inject a querystring to the SQL statement.

Looks like I would have to go for the conventional method, e.g. place two images (one up arrow and one down arrow) to represent the ASC and DESC respectively. So when I click on either one of them, they will inject the "sort" string to the SQL.

I have my initial thoughts of using just one link to achieve that. I encountered this in phpMyAdmin, exactly what I want but I simply can't decipher how they code that. Their code is just too complex for me :)

The difference between my code and yours is that you need to check if there is a sort on that column before you build and display the link. In your code, it's always ascending, no ?

But now that i reread it, i see that you want to have this sort option on each variable.

So you need to do this dynamically, by indeed adding the variablename (columnname) also to the querystring and then, when you build the table, check if the variable name from the recordset is the same as the one from the querystring.

//build a default header for both ascending and descending link
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
$orderlink .= "&nbsp;&nbsp;" ;
$orderlink .= ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;

if $_GET['columnname']=="proposalno" {
if ($order=="asc") {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ; // default replaced by single link
} else {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=proposalno&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;
}
}
echo $orderlink ;

But to make this realy generic (like it probably is in phpMyAdmin) you just need to get the columnname (with mysql_field_name() )and then replace all 'proposalno' by that fieldname.

+ you need to place the above code in a for-loop with an incrementing variable for the number of columns. It would look like

$num_fields= mysql_num_fields($query_result );
if ($num_fields) { //build the order by row
echo "<tr>" ;
for ($i = 0; $i < $num_fields; $i++) {
$colname = mysql_field_name($query_result , $i) ;
//build a default header for both ascending and descending link
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=" . $colname . "&sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ;
$orderlink .= "&nbsp;&nbsp;" ;
$orderlink .= ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=" . $colname . "&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;

if $_GET['columnname']== $colname {
if ($order=="asc") {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=" . $colname ." &sort=desc\" title=\"Sort the records in descending order\">Sort descending</a>") ; // default replaced by single link
} else {
$orderlink = ("<a href=\"" . $_SERVER["PHP_SELF"] . "?columnname=" . $colname . "&sort=asc\" title=\"Sort the records in ascending order\">Sort ascending</a>") ;
}
}
echo ("<td>" . $orderlink . "</td>" ; // place each link-couplein a new cell
}
echo "</tr>" ;
} else {
echo "Empty recordset" ;
}
//and then here comes your code to display the records from the recordset.
while ($row = @mysql_fetch_array ($query_result)) {
...


It's untested code, but it should be debugable ...

Hi Raf, I will try them out. Thanks! :thumbsup: