Reorder Select elements in order to sort DB table
3.00/5 (1 vote)
JavaScript code that will rearrange the elements in a select control according to the user selection.
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:
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 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.