Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SortTable js not recognizing numeric data

Posted on 2006-11-09
6
597 Views
Last Modified: 2011-08-18
On this page, http://sshomes.info/estrellaprices.cfm,
I have a table using the very clever SortTable javascript ( code below )
but I am having an issue.

The Price column is being sorted alphanumerically, instead of numerically,
so that 5,000 is being sorted after 10,000 and 20,000.
( It makes no difference whether I remove the $ or commas from the data ).

However, the lot numbers in the first column are sorting correctly by number.

Setup of this script is extremely simple, and I have not made any alterations to the js code. I just cannot figure out why the first column is automatically recognized as numeric, but not the price column.

I think the key lies in these lines - but I am not a javascript reader/writer
===
    if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
    if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
===

Below is the entire JS code I am using - and of course the source of the page shows the table structure.
Any help would be greatly appreciated.


==================================

addEvent(window, "load", sortables_init);

var SORT_COLUMN_INDEX;

function sortables_init() {
    // Find all tables with class sortable and make them sortable
    if (!document.getElementsByTagName) return;
    tbls = document.getElementsByTagName("table");
    for (ti=0;ti<tbls.length;ti++) {
        thisTbl = tbls[ti];
        if (((' '+thisTbl.className+' ').indexOf("sortable") != -1) && (thisTbl.id)) {
            //initTable(thisTbl.id);
            ts_makeSortable(thisTbl);
        }
    }
}

function ts_makeSortable(table) {
    if (table.rows && table.rows.length > 0) {
        var firstRow = table.rows[0];
    }
    if (!firstRow) return;
   
    // We have a first row: assume it's the header, and make its contents clickable links
    for (var i=0;i<firstRow.cells.length;i++) {
        var cell = firstRow.cells[i];
        var txt = ts_getInnerText(cell);
        cell.innerHTML = '<a href="#" class="sortheader" '+
        'onclick="ts_resortTable(this, '+i+');return false;">' +
        txt+'<span class="sortarrow">&nbsp;&nbsp;&nbsp;</span></a>';
    }
}

function ts_getInnerText(el) {
      if (typeof el == "string") return el;
      if (typeof el == "undefined") { return el };
      if (el.innerText) return el.innerText;      //Not needed but it is faster
      var str = "";
      
      var cs = el.childNodes;
      var l = cs.length;
      for (var i = 0; i < l; i++) {
            switch (cs[i].nodeType) {
                  case 1: //ELEMENT_NODE
                        str += ts_getInnerText(cs[i]);
                        break;
                  case 3:      //TEXT_NODE
                        str += cs[i].nodeValue;
                        break;
            }
      }
      return str;
}

function ts_resortTable(lnk,clid) {
    // get the span
    var span;
    for (var ci=0;ci<lnk.childNodes.length;ci++) {
        if (lnk.childNodes[ci].tagName && lnk.childNodes[ci].tagName.toLowerCase() == 'span') span = lnk.childNodes[ci];
    }
    var spantext = ts_getInnerText(span);
    var td = lnk.parentNode;
    var column = clid || td.cellIndex;
    var table = getParent(td,'TABLE');
   
    // Work out a type for the column
    if (table.rows.length <= 1) return;
    var itm = ts_getInnerText(table.rows[1].cells[column]);
    sortfn = ts_sort_caseinsensitive;
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
    if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;
    SORT_COLUMN_INDEX = column;
    var firstRow = new Array();
    var newRows = new Array();
    for (i=0;i<table.rows[0].length;i++) { firstRow[i] = table.rows[0][i]; }
    for (j=1;j<table.rows.length;j++) { newRows[j-1] = table.rows[j]; }

    newRows.sort(sortfn);

    if (span.getAttribute("sortdir") == 'down') {
        ARROW = '&nbsp;&nbsp;&uarr;';
        newRows.reverse();
        span.setAttribute('sortdir','up');
    } else {
        ARROW = '&nbsp;&nbsp;&darr;';
        span.setAttribute('sortdir','down');
    }
   
    // We appendChild rows that already exist to the tbody, so it moves them rather than creating new ones
    // don't do sortbottom rows
    for (i=0;i<newRows.length;i++) { if (!newRows[i].className || (newRows[i].className && (newRows[i].className.indexOf('sortbottom') == -1))) table.tBodies[0].appendChild(newRows[i]);}
    // do sortbottom rows only
    for (i=0;i<newRows.length;i++) { if (newRows[i].className && (newRows[i].className.indexOf('sortbottom') != -1)) table.tBodies[0].appendChild(newRows[i]);}
   
    // Delete any other arrows there may be showing
    var allspans = document.getElementsByTagName("span");
    for (var ci=0;ci<allspans.length;ci++) {
        if (allspans[ci].className == 'sortarrow') {
            if (getParent(allspans[ci],"table") == getParent(lnk,"table")) { // in the same table as us?
                allspans[ci].innerHTML = '&nbsp;&nbsp;&nbsp;';
            }
        }
    }
       
    span.innerHTML = ARROW;
}

function getParent(el, pTagName) {
      if (el == null) return null;
      else if (el.nodeType == 1 && el.tagName.toLowerCase() == pTagName.toLowerCase())      // Gecko bug, supposed to be uppercase
            return el;
      else
            return getParent(el.parentNode, pTagName);
}
function ts_sort_date(a,b) {
    // y2k notes: two digit years less than 50 are treated as 20XX, greater than 50 are treated as 19XX
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]);
    if (aa.length == 10) {
        dt1 = aa.substr(6,4)+aa.substr(3,2)+aa.substr(0,2);
    } else {
        yr = aa.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt1 = yr+aa.substr(3,2)+aa.substr(0,2);
    }
    if (bb.length == 10) {
        dt2 = bb.substr(6,4)+bb.substr(3,2)+bb.substr(0,2);
    } else {
        yr = bb.substr(6,2);
        if (parseInt(yr) < 50) { yr = '20'+yr; } else { yr = '19'+yr; }
        dt2 = yr+bb.substr(3,2)+bb.substr(0,2);
    }
    if (dt1==dt2) return 0;
    if (dt1<dt2) return -1;
    return 1;
}

function ts_sort_currency(a,b) {
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]).replace(/[^0-9.]/g,'');
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]).replace(/[^0-9.]/g,'');
    return parseFloat(aa) - parseFloat(bb);
}

function ts_sort_numeric(a,b) {
    aa = parseFloat(ts_getInnerText(a.cells[SORT_COLUMN_INDEX]));
    if (isNaN(aa)) aa = 0;
    bb = parseFloat(ts_getInnerText(b.cells[SORT_COLUMN_INDEX]));
    if (isNaN(bb)) bb = 0;
    return aa-bb;
}

function ts_sort_caseinsensitive(a,b) {
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]).toLowerCase();
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]).toLowerCase();
    if (aa==bb) return 0;
    if (aa<bb) return -1;
    return 1;
}

function ts_sort_default(a,b) {
    aa = ts_getInnerText(a.cells[SORT_COLUMN_INDEX]);
    bb = ts_getInnerText(b.cells[SORT_COLUMN_INDEX]);
    if (aa==bb) return 0;
    if (aa<bb) return -1;
    return 1;
}


function addEvent(elm, evType, fn, useCapture)
// addEvent and removeEvent
// cross-browser event handling for IE5+,  NS6 and Mozilla
// By Scott Andrew
{
  if (elm.addEventListener){
    elm.addEventListener(evType, fn, useCapture);
    return true;
  } else if (elm.attachEvent){
    var r = elm.attachEvent("on"+evType, fn);
    return r;
  } else {
    alert("Handler could not be removed");
  }
}

=======================================



0
Comment
Question by:MichaelEvangelista
  • 4
  • 2
6 Comments
 
LVL 6

Expert Comment

by:sodalitas
ID: 17909194
Looks to me like it's not matching any of the expressions and defaulting the sort function to text.  Quick way to validate:
in ts_ResortTable replace

    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
    if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;

with

    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d\d\d$/)) { sortfn = ts_sort_date; alert("sorting by date"); }
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d$/)) { sortfn = ts_sort_date; alert("sorting by date"); }
    if (itm.match(/^[£$]/)) { sortfn = ts_sort_currency; alert("sorting by currency");}
    if (itm.match(/^[\d\.]+$/)) { sortfn = ts_sort_numeric; alert("sorting by numeric");}

If you resort by price and don't see one of those alerts, you know it's sorting by default, which is text.  At that point you just need to change your regular expression to match the format of your data, or add a new one.
0
 

Author Comment

by:MichaelEvangelista
ID: 17909720
Thanks - very slick test... and you are right, no alert.
So, on to phase 2

>> At that point you just need to change your regular expression to match the format of your data, or add a new one.

I figured the fix would be something like this, but I am not great with RegExp's.
Can you help me with this?
0
 

Author Comment

by:MichaelEvangelista
ID: 17909729
I am assuming the fix is in these lines..

    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^\d\d[\/-]\d\d[\/-]\d\d$/)) sortfn = ts_sort_date;
    if (itm.match(/^[£$]/)) sortfn = ts_sort_currency;
    if (itm.match(/^[\d\.]+$/)) sortfn = ts_sort_numeric;

But it seems, with the $ in the data, it should already be sorting by currency?
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 6

Accepted Solution

by:
sodalitas earned 500 total points
ID: 17914288
(/^[£$]/)
It looks to me like this regular expression is only looking for the optional pound or dollar symbol, you want to look for the optional pound or number symbol followed by a number of integers with optional commas followed optionally by a decimal point and two integers.

Something like this (untested):
(/^[£$]\d{1,3}(,\d{3})*[\.\d{2}]/)

This says "start of string"
/^
optionally a symbol
[£$]
followed by between 1 and 3 decimal numbers
d{1,3}
followed by 0 or more sets of comma and 3 decimal numbers
(,\d{3})*
optionally follwed by a decimal point and two decimal numbers
[\.\d{2}]
end of string
/
0
 

Author Comment

by:MichaelEvangelista
ID: 17915289
I was so sure that was going to fix it... but it appears there is more to the story.

I just spent an hour or so testing various possibilities - took that column out altogether and replaced it with a simple sequence of numbers, still no luck sorting numerically.. so it isnt the $ sign or commas... then what the...? more hunting...

Apparently this script does not like the fact that, if there is no price, I was inserting
&nbsp; into the cell. I even tried $&nbsp; , $-- , or just plain --... no luck. It wanted a dollar sign and numbers in every cell in that column.  When I put in $0, everything worked just like it should! ( aha!... but of course, client doesnt want $0 showing... )

So, funky as it may be, I found something that appears to be working.
If there is no price to be shown, the cell now gets
<span style="display:none">$0</span>
which inserts a $0 price, but tells the browser not to show it.

As far as I can tell, this works just fine.

I will accept your answer because, if things were working the way I thought they should have been, you would have been right on the money. That and your testing definitely led me to find a solution that worked.

Thanks again for your time and support.

 
0
 

Author Comment

by:MichaelEvangelista
ID: 17915304
Hmmm..... afterthought... I bet I could come up with a reg-ex that allows for that &nbsp; entry, couldn't I ? Oh well... it is working, dont want to mess with it now!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
alert(innerHTML); 8 32
Not allowed to load local recource. 4 35
forgetful about js objects 8 46
Button function on table is in trouble 3 22
This article shows how to create and access 2-dimensional arrays in JavaScript.  It includes a tutorial in case you are just trying to "get your head wrapped around" the concept and we'll also look at some useful tips for more advanced programmers. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question