Forums

There have already been a few examples in the forums on how to add "sort" to ChronoConnectivity results.

There is some neat JavaScript code (see http://www.kryogenix.org/code/browser/sorttable for an example) that can be used which makes your table sortable without too much fuss. The table is sorted without re-querying the database so this is efficient. Unfortunately, if you are using colspan and rowspan in your tables, you may not be able to use the JavaScript method.

I will now explain a simple alternative sort method where you can format your rows and columns however you want. Note that this method is not especially efficient because it queries the database every time the page is sorted! This method does not require any changes to the Chronoforms or ChronoConnectivity code.

Here is an example:



PHP code is used to insert "ORDER BY <variable>" into the "WHERE SQL" area with the sort criteria added as HTML links in the HEADER section.

Note that you should not put anything in the "Order fields" area for this to work. You will also need a WHERE statement. As a workaround, you could use "WHERE cf_id > 0" (or something similar) if you don't actually need to use a WHERE statement!

Example code is shown below. In the example, a default sort order is included so that the books are sorted with the most recent titles appearing first.

Neil.

WHERE SQL:

<?php
  $order = JRequest::getVar('Order');
  if ( $order ) echo "WHERE check1='Upper Primary' AND check12='Making a Living' ORDER BY " . "$order";
    else
  echo "WHERE check1='Upper Primary' AND check12='Making a Living' ORDER BY " . "PublicationYear DESC";
?>

HEADER:

...
<td align="right">
SORT BY:
<a href="index.php?option=com_chronoconnectivity&connectionname=Books&Itemid=52&Order=Title">Title</a> |
<a href="index.php?option=com_chronoconnectivity&connectionname=Books&Itemid=52&Order=Author">Author</a> |
<a href="index.php?option=com_chronoconnectivity&connectionname=Books&Itemid=52&Order=PublicationYear DESC">Most Recent</a>
</td>
...

Hi Paula,

I recently discovered a small problem if pagination is enabled. If you sort by "Most Recent" and then try to go to a subsequent page there is a MySQL error. This occurs if there is a space in the order syntax such as when you append DESC to a field name and the space is inconveniently encoded in the url. To get around this problem, I added an additional line to rewrite $order each time the screen is redisplayed:

<?php
  $order = JRequest::getVar('Order');
  if (substr($order,0,15) == 'PublicationYear') $order = "PublicationYear DESC"; // ADDED THIS LINE
  if ( $order ) echo "WHERE check1='Upper Primary' AND check12='Making a Living' ORDER BY " . "$order";
    else
  echo "WHERE check1='Upper Primary' AND check12='Making a Living' ORDER BY " . "PublicationYear DESC";
?>

There's probably a neater way to fix this and I'd be happy to hear ideas from anyone.

Thanks, it's nice to get some feedback after 497 page views on the post!

Neil