JQuery Tablesorter - Save Sort Order

I had a request from my client to save the order of the columns on a complex view of data so that when they select a record and go to a new window to edit that record, then come back, they can see the data in the same order as it was without having to re-click the appropriate column headers. I could find bits and pieces of code, but nothing presented as a full solution. So, I've documented my steps here.

Once I had the code working as I wanted, I put the submit() code into a php window. I tried to call the code in the onunload event of the window so that I didn't negate the benefits of using jquery to sort without having to refresh the window each time. However, once I did that, the code executed about 90% of the time - having to do with asynchronous processing of the windows, meaning that while the php code was executing, the next window came up and stopped the code from running. This, even though I included all the right statements in my php code (ignore_user_abort(true); and set_time_limit(0);). I was left with a choice between forcing the user to click a button to save the sort settings or to save behind the scenes every time the user changed the sort order. In the interests of performance, I went with the button, thought not very happily. I also added pagination, which came with a new set of issues in that you can't combine hidden rows with pagination. I will write a follow-up post down the road.

Feel free to comment. I apologize for having to replace the < and > signs with * in order for the text to render properly. I imagine when I redesign my website (coming soon!) and convert to WordPress I'll be able to format this in a way that is more user-friendly.

Setup a table to hold the sort order (1st 3 columns are the primary key; I didn't put that here):

CREATE TABLE Sort_SS (
   pract_id int(11) NOT NULL,
   frm varchar(50) NOT NULL,
   col int(11) NOT NULL default '0', -- the column number, starting w/ 0
   sortfl tinyint(1) NOT NULL default '0', -- 0 for ascending, 1 for descending
   sortorder int(11) NOT NULL default '0' -- the order of column headers selected when multiples
)  

To your form:
Add a hidden variable to the form to hold the javascript array/sortList when the list changes:

*form method=post name="ListView" action="../../includes/saveSort.php"*
  * !--critical to set the value="" to avoid a loop--*
  *input name="arySort" type=hidden value=""*
  * !--I am calling saveSort from multiple forms, so pass a form name--*
   *input name="frm" type=hidden value="SavedSessions"*
*/form*

Add code to the head tag to read the table and create the sortlist:

*?php
   //using a php wrapper found here:
   //http://www.ricocheting.com/scripts/php_mysql_wrapper.php
   $sql="SELECT col, sortfl from Sort_SS where pract_id = $pract_id and frm='SavedSessions' order by sortorder";
   $rows = $db->;fetch_all_array($sql);
   $cols = array();
   $cols['col']=array();
   $cols['sortfl']=array();
   foreach($rows as $record){
      array_push($cols['col'], $record['col']);
      array_push($cols['sortfl'], $record['sortfl']);
   }
?*

*script type="text/javascript" id="js"*
   var cols = new Array();
   *?
      for ($i=0; $i< count($cols['col']); $i++) {
         echo 'cols['.$i.']= ['. $cols['col'][$i] .','. $cols['sortfl'][$i] .'];';
      }
   ?*
   $(document).ready(function() {
      $("#sessions").tablesorter({
         sortList:cols
      });
   });

To jquery.tablesorter.js - add at the start of the function multisort:

function multisort(table,sortList,cache) {
   //populate the form variable for use in the submit - checking for > "" is
   //CRITICAL and keeps it from submitting when the form first loads and getting into a loop
   if(document.ListView.arySort.value > ""){
      document.ListView.arySort.value = sortList;
      document.ListView.submit();
      return;
   }else{
   document.ListView.arySort.value = sortList;
}
//then the rest of the function follows as is...
if(table.config.debug) { var sortTime = new Date(); }}

And at the start of the function appendToTable:

function appendToTable(table,cache) {
//if you submit and return from the multisort, you might end up here and error w/out this stmt
if(!cache){return;}
//then the rest of the function follows as is...
if(table.config.debug) {var appendTime = new Date()}

Finally, here is my saveSort.php file:

*?php
   session_start();
   if(!session_is_registered(myusername)){
      header("location:http://myurl/Login/");
   }
   require("./config.inc.php");
   require("./Database.class.php");
   $db = new Database(DB_SERVER, DB_USER, DB_PASS, DB_DATABASE);
   $db->connect();
   //Makes Session Variables available to this page
   require "./common_variables.php";
   //Retrieve the hidden form variable (using PHP).
   $sort = $_POST['arySort'];
   $frm = $_POST['frm'];
   //clear out existing records for the practitioner
   $sql = "DELETE FROM Sort_SS WHERE pract_id=$pract_id and frm='$frm'";
   $db->query($sql);
   $aryChars = array("[","]");
   //get rid of the brackets so is comma delimited list
   $sort = str_replace($aryChars, "", $sort);
   //then break out the list
   $arySort = explode(",",$sort);
   $col = '';
   $sortorder = 0;
   //preset these static values for the insert statement
   $data['pract_id']=$pract_id;
   $data['frm']=$frm;
   //loop through the array and get the values to add
   foreach ($arySort as $value) {
      if(is_numeric($value)){
         if($col==''){
            $col = $value;
         }else{
            $data['col']=$col;
            $data['sortfl']=$value;
            $data['sortorder']=$sortorder++;
            $col = '';
            $db->query_insert('Sort_SS', $data);
         }
      }
   }
?*