Click here to Skip to main content
15,867,704 members
Articles / Programming Languages / Javascript

Reorder Select elements in order to sort DB table

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
6 Nov 2007CPOL1 min read 26.1K   7   2
JavaScript code that will rearrange the elements in a select control according to the user selection.

Screenshot - demo.gif

Introduction

This client-side JavaSscript code enables to transform a multiple Select element into a sorted control that can help in producing the "order by" clause of a SQL query, and with that sort the table records accordingly to the selected elements.

If we have a DB table that has five columns and we would like to sort the date according to columns 3, 2, 1, all we will need to do is to click on those columns in that order in the multiple Select element that will reorder the list of its options accordingly.

This code deals only with the client side and not the actual data retrieval, even thought the principle is simple. We connect together the SQL query with the list of columns delimited by comma, after the order by clause. For example:

SQL
Select column1, column2, column3, column4, column5 from TableX 
  where ConditionY order by (columns names from the reorder select control )

The reorder select control holds the columns of the table as its options. On each click on one of the options, the code will bring the first selected option to the top of the list. In the second selection, the selected element will become the second on the list and so on... In the end, you will get a new sorted list of columns you can sort your table by. When the form is submitted, a hidden element holds the reordered list and you will be able to use the value of this element in your SQL query generator code.

Just copy paste the code to a new page:

HTML
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>test page</title>
</head>
<body>
<script type="text/jscript">
function sortedSelectClass(htmlSelectElementObj){
    //alert(htmlSelectElementObj);
   
    var cursor = 0; 
    // local variable holds the position the next selected option will be placed
    // by declaring variable with the var keyword we Encapsulating data hiding it
    // making it self-contained in the class. only methods invoked by the class will
    // have access to this variable. 

    // index of the selected element
    var lastSelectedIndex=0;
    // obj the selected element
    var selectedElement = null;
    // object containing the html select element.
    var HTMLControl   = htmlSelectElementObj;
    // number of options in the select element.
    var controlLength = htmlSelectElementObj.length;
    // save the original options order.
    var savedEelemntsValuse = new Array(controlLength)
    var savedEelemntsText = new Array(controlLength)
    var selectedElementsList ='';
     
    // initiailize
    for(var i=0;i<controlLength;i++){
       savedEelemntsValuse[i]=HTMLControl[i].value;
       savedEelemntsText[i]=HTMLControl[i].text;
       HTMLControl[i].selected = false;
    }   
            
     //********************************************
     // returns the last selected option as object
     // and set the lasSelectedIndex to the selected
     // element index
     //********************************************
     this.selectItem = function(){
            var rv;
            for (var i=0; i< controlLength; i++) {
              if (HTMLControl[i].selected) {
                selectedElement = HTMLControl[i];
                lastSelectedIndex = i;
              }//--> end if
            }//--> end for
     }//--> end function
               
           
    this.move = function(){
           selectedElementsList =""; 
           // move element
           this.selectItem() ;
           // through the elements get the selected/ clicked 
           //  and initialize the local object selectedElement  
           // increase the cursor position only if it is not out of the boundary 
           // of the elements length and the selected item is not already selected
           // this can be done by comparing the last selected index to the current
           // cursor position and make sure that the index is at least equal or bigger 
           // then the current cursor.
   
    if (cursor < controlLength  && lastSelectedIndex >= cursor   ){
          // save the selected element value and text those 
          // attributes will be             
          var priviosObjectValue = HTMLControl[cursor].value;
          var priviosObjectText =  HTMLControl[cursor].text; 
          HTMLControl[cursor].value =  selectedElement.value;
          HTMLControl[cursor].text  =  selectedElement.text;
          HTMLControl[cursor].style.color='red';
                 
          HTMLControl[lastSelectedIndex].value = priviosObjectValue;
          HTMLControl[lastSelectedIndex].text = priviosObjectText;
          cursor++;
   }
   else if (cursor >= controlLength){
                         cursor=0; 
   }
   else{
       cursor=cursor; 
   }
   
   // clear selected element and save the already selected
   // one to a CSV string.
 
   for(var i=0;i<controlLength;i++){
       HTMLControl[i].selected = false;
       if(HTMLControl[i].style.color=='red')
           selectedElementsList += HTMLControl[i].value + ","; 
   }//--> end for            
                               
   }//--> end function
           
   //*********************************
   //    restore the orginal list
   //*********************************
   this.clear = function(){
        for(var i=0;i<controlLength;i++){
           HTMLControl[i].value = savedEelemntsValuse[i];
           HTMLControl[i].text  = savedEelemntsText[i];
           HTMLControl[i].selected = false;
           HTMLControl[i].style.color='black';
        } 
        cursor=0; 
        lastSelectedIndex=0; 
        selectedElement = null;     
    } 
             
    //*********************************
    //    get the selected columns by order
    //    as csv without the last comma
    //*********************************
    this.getSelectedElements = function(){
        var rv="";
        if (selectedElementsList !=  null && selectedElementsList.indexOf(',')!= -1)
           rv =  selectedElementsList.substring(0,(selectedElementsList.length)-1)
       return rv;    
     }//--> end function 
        
}//--> end class
               
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
//                      end of class you are dismissed 
// Alfi known as Alfonso use it as you like
//^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
            
function  showParam(theSortedColumns){
    alert(theSortedColumns);
}          

</script>
<form id="form1" onsubmit="showParam(selectClassObj1.getSelectedElements())">
  <table>
      <tr>
        <td id=selectObj1>
        <select name="test" multiple="multiple" 
           onchange="selectClassObj1.move()" size="5">
        <option value="1">op1</option>
        <option value="2">op2</option>
        <option value="3">op3</option>
        <option value="4">op4</option>
        <option value="5">op5</option>
        </select>
        </td>
      </tr>
      <tr>
        <td><input type="button" name="clear" 
           value="Clear" onclick="selectClassObj1.clear();" /></td>
      </tr>
      <tr>
        <td> </td>
      </tr>
      <tr><td><input type="submit" value="submit" /></td></tr>
  </table>
</form>
</body>
<script>
     // initiate the object
     var selectClassObj1 = new sortedSelectClass(document.all.test);   
</script>
</html>

Version control

  • Bug fix: 11/06/07: selecting an element that was already selected, in reply to juvo.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
United States United States
web developer,
windows based applications ,
data specialist.
java,c#,asp,jsp,vb

Comments and Discussions

 
Generalnice but not perfect Pin
juvo@6-Nov-07 5:19
juvo@6-Nov-07 5:19 
GeneralRe: nice but not perfect Pin
alfi uziel6-Nov-07 7:24
alfi uziel6-Nov-07 7:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.