filtering and sorting a recordset

Hi everyone,

I am trying to write a client side java script that allows me to filter and sort a recordset.

I need for this script to do the sorting and the filtering on the client side because the recordset is already opened and I don't need to write an SQL statement to access the database on the server a second time.

Is there anybody out there who can help me with that, I would really appreciate that.

What exactly are you trying to do?
Is there a reason that you can't use VB Script?

Steve

------------------
Web Application Developer
URL

Hi steve,

I have this report that I am generating. In this report I have headers and details. What I am trying to do is to give the user the ability to sort the results of the report by clicking on one of the headers of this report.

Let's say for example that one of the headers in the report is "Date",I need to give the user the choice to click on this "date" header and sort the report by it.

It doesn't have to be a Java script. But I am trying to avoid writing a select statement and connect to the SQL server again to retrieve a new recordset filtered in the way I want it. I tought by writing a Java script I'll be able to do that on the client side and this will make it faster.

Any suggestion ? I would appreciate that.

The method is pretty straightforward:

When reading the original recordset from the server, create a Javascript array of the values. Then document.write() these values into an HTML table. Whenever the user clicks on a header, you call a function to re-index the array, then rewrite the table with the new indexing order. See http://developer.netscape.com/docs/...jsref/array.htm for a starting place.

I did a small client-side search engine using Javascript arrays, which you can see at http://www.anisite.com/entercoords.htm It's a geographical radio tower search form; enter latitude(24,40,0) and longitude(81,27,0) for an example search.

Thanks for your help, I'll try it and see what happens. My HTML code is a little bit complicated and to implement this idea in it is going to be a little tough but I'll try it.

By the way is there any function in VB or JAVA scripting that will allow me to comma delimited a recordset while I am reading it from the server ?
I have the code that will sort the data for me but I have to have it in a comma delimited file.

Thanks again.

<BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rycamor:
The method is pretty straightforward:

When reading the original recordset from the server, create a Javascript array of the values. Then document.write() these values into an HTML table. Whenever the user clicks on a header, you call a function to re-index the array, then rewrite the table with the new indexing order. See http://developer.netscape.com/docs/...jsref/array.htm for a starting place.

I did a small client-side search engine using Javascript arrays, which you can see at http://www.anisite.com/entercoords.htm It's a geographical radio tower search form; enter latitude(24,40,0) and longitude(81,27,0) for an example search. [/quote]

Javascript can load a list of values into an array; it just remains to you to output that list from the database into the HTML page in the right format. What server-side language are you using? What database?

If you are using PHP, I believe there is an old article on Devshed about loading Javascript arrays from a database.

[This message has been edited by rycamor (edited October 30, 2000).]

Hi there,

Thanks for replying. I am using VB scripting
as a server-side language and a database that resides on a SQL server.

I have this Java script that I found on the web. It does sort things the way I want it to sort.
But the problem is that I can't modify it to toggle back and forth. what I mean is, if I click on a header to sort records by this header it does the work, but I need it to sort the records back to the same way they were before if I clicked the same header again. Can you help me with that ?
Here is the Script :


<SCRIPT LANGUAGE="JavaScript"><!--
function myObject(number,text,date,name) {
this.number = number;
this.text = text;
this.date = date;
this.name = name;
}

var objectArrayIndex = 0;
var myObjectArray = new Array();

function showObjectArray(object,length) {
var output = '<CENTER><TABLE BORDER=1>';
output += '<TR>' +
'<TH><A HREF="sort.html?number">number</A></TH>' +
'<TH><A HREF="sort.html?text">text</A></TH>' +
'<TH><A HREF="sort.html?date">date</A></TH>' +
'<TH><A HREF="sort.html?name">name</A></TH>' +
'</TR>';
for (var i=0; i<length; i++)
output += '<TR>' +
'<TD>' + object[i].number+ '</TD>' +
'<TD>' + object[i].text + '</TD>' +
'<TD>' + object[i].date + '</TD>' +
'<TD>' + object[i].name + '</TD>' +
'</TR>';
output += '</TABLE></CENTER>';
document.write(output);
}

function myObjectBubbleSort(arrayName,length,property) {
for (var i=0; i<(length-1); i++)
for (var j=i+1; j<length; j++)
if (eval('arrayName[j].' + property + '<arrayName[i].' + property)) {
var dummy = arrayName[i];
arrayName[i] = arrayName[j];
arrayName[j] = dummy;
}
}

myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');
myObjectArray[objectArrayIndex++] = new myObject(400,'zzz','19961003','You');
myObjectArray[objectArrayIndex++] = new myObject(300,'fox','19950902','Them');
myObjectArray[objectArrayIndex++] = new myObject(50,'bad','19981201','Us');

var sortProperty = window.location.search.substring(1);

if (sortProperty.length != 0)
myObjectBubbleSort(myObjectArray,objectArrayIndex,sortProperty);

showObjectArray(myObjectArray,objectArrayIndex);
//--></SCRIPT>


If you can also explain this script a little bit more, I would appreciate that.

I have modified your script a bit and it now sorts both ways -

<SCRIPT LANGUAGE="JavaScript"><!--

function myObject(number,text,date,name) {
this.number = number;
this.text = text;
this.date = date;
this.name = name;
}

var objectArrayIndex = 0;
var myObjectArray = new Array();

function showObjectArray(object,length) {
if (sortMode=='desc'){
sortMode='asc';
}else{
sortMode='desc';
}
var output = '<CENTER><TABLE BORDER=1>';
output += '<TR>' +
'<TH><A HREF="sortTest.htm?sortBy=number&mode=' + sortMode + '">number</A></TH>' +
'<TH><A HREF="sortTest.htm?sortBy=text&mode=' + sortMode + '">text</A></TH>' +
'<TH><A HREF="sortTest.htm?sortBy=date&mode=' + sortMode + '">date</A></TH>' +
'<TH><A HREF="sortTest.htm?sortBy=name&mode=' + sortMode + '">name</A></TH>' +
'</TR>';
for (var i=0; i<length; i++)
output += '<TR>' +
'<TD>' + object[i].number+ '</TD>' +
'<TD>' + object[i].text + '</TD>' +
'<TD>' + object[i].date + '</TD>' +
'<TD>' + object[i].name + '</TD>' +
'</TR>';
output += '</TABLE></CENTER>';
document.write(output);
}

function myObjectBubbleSort(arrayName,length,property) {
for (var i=0; i<(length-1); i++)
for (var j=i+1; j<length; j++)
if (sortMode=='desc'){
if (eval('arrayName[j].' + property + '<arrayName[i].' + property)) {
var dummy = arrayName[i];
arrayName[i] = arrayName[j];
arrayName[j] = dummy;
}
}else{
if (eval('arrayName[j].' + property + '>arrayName[i].' + property)) {
var dummy = arrayName[i];
arrayName[i] = arrayName[j];
arrayName[j] = dummy;
}
}
}

myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');
myObjectArray[objectArrayIndex++] = new myObject(400,'zzz','19961003','You');
myObjectArray[objectArrayIndex++] = new myObject(300,'fox','19950902','Them');
myObjectArray[objectArrayIndex++] = new myObject(50,'bad','19981201','Us');


//read querystring
var queryString = unescape(location.search.replace(/+/g,' '));
queryString = queryString.substring(1).split('&');
for(i=0;i<queryString.length;i++){
queryString[i] = queryString[i].split('=');
}
var sortProperty=queryString[0][1];
var sortMode=queryString[1][1];


if (sortProperty.length != 0)
myObjectBubbleSort(myObjectArray,objectArrayIndex,sortProperty);

showObjectArray(myObjectArray,objectArrayIndex);
//--></SCRIPT>

Basically I added another parameter to the querystring to specify a mode of either 'asc' or 'desc'. Then the code sorts depending on this mode. I might have the ascending and descending the wrong way around - I can never remember which way round it is!!

I also had to add more sophisticated code to read the querystring as 2 parameters were being passed in it instead of 1.

Hope you can understand this, if not I'll try and explain it in more detail....

Matt.

Thanks for the adjustment.

But I am getting an error message on this line :

var sortProperty = queryString[0][1];

It says that 'queryString.1.1' is not an object.

Ooops! You need to change the code that reads the querystring to this -

var queryString = unescape(location.search.replace(/+/g,' '));
if (queryString!=""){
queryString = queryString.substring(1).split('&');
for(i=0;i<queryString.length;i++){
queryString[i] = queryString[i].split('=');
}
var sortProperty=queryString[0][1];
var sortMode=queryString[1][1];
}else{
var sortProperty="NoProps";
}


All it does is check if there is NO querystring i.e. when you first load the page, and if so does not try to read the values from it. Sorry about that!

Matt.

Matt,

Thanks very much, it is working like a charm.
I really appreciate you help very much.

3 things that I need to know :

1st- in this piece of code

var queryString = unescape(location.search.replace(/+/g,' '));

Why are you trying to replace the + sign with a space in the expression string ?
There is no + sign in it at all!!


2nd- In case I am trying to sort data straight from a record set and not by sending some hardcoded values to the myObject function like the script is working right now, how this script would be modified to do this ?

3rd- Does

"myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');"

mean that I am trying to increment the index of the array by one and assign the results of the function to it ?

Please advise, I am trying to understand the script very well.



[This message has been edited by Chad (edited November 01, 2000).]

Chad,

The querystring reading bit of script comes from a more general script that I wrote a while ago. Some parameters within querystrings will have + signs in them which may be used to build database queries. For example, part of the querystring for the page I'm on now in devshed is this -

TopicSubject=filtering+and+sorting+a+recordset

If you were wanting to strip that parameter out of the querystring and display it on a page, you would want to replace the + signs with spaces. That's why this script checks for + signs.

In your particular case, you don't have any + signs in your querystring and hence could get rid of that check. You could just use -

var queryString = location.search;

- instead.

Hope that sheds some light on it.

By the way, I'm in the UK so not sure about the time difference?

Matt.

Thanks Matt,
That explains part of it.
I really appreciate you help very much.

2 other things that I need to figure out:


1st- In case I am trying to sort data straight from a record set and not by sending some hardcoded values to the myObject function like the script is working right now, how this script would be modified to do this ?

2nd- Does

"myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');"

mean that I am trying to increment the index of the array by one and assign the results of the function to it ?

Please advise, I am trying to understand the script very well.

Chad,

You are right -

myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');

- does mean that you are incrementing the array and setting the results of the function to it.

For your other point, it all depends what server-side technology you are using. In fact, looking at your script again, it works by using <a hrefs> to refresh the page with a different sort criteria. If you were using a .asp page to build your table, everytime you clicked a link to sort the page it would go back to the server to refresh the page! Which is exactly what you don't want it to do!!

So, if you are using server-side code to build the page, you really need a script that won't hit the server again after the page is initially loaded. I've modified your script (again!) to hopefully do this. However, I have used a <div> tag to hold the table, so hopefully this is no problem for you. Here is the entire html file -

<html>
<head>
<SCRIPT LANGUAGE="JavaScript"><!--

function myObject(number,text,date,name) {
this.number = number;
this.text = text;
this.date = date;
this.name = name;
}

var objectArrayIndex = 0;
var myObjectArray = new Array();
var sortMode='desc';
var output
function showObjectArray(object,length) {
if (sortMode=='desc'){
sortMode='asc';
}else{
sortMode='desc';
}
var str1 = "javascript:myObjectBubbleSort(myObjectArray,objectArrayIndex,'number');"
var str2 = "javascript:myObjectBubbleSort(myObjectArray,objectArrayIndex,'text');"
var str3 = "javascript:myObjectBubbleSort(myObjectArray,objectArrayIndex,'date');"
var str4 = "javascript:myObjectBubbleSort(myObjectArray,objectArrayIndex,'name');"

output = '<TABLE BORDER=1>';
output += '<TR>' +
'<TH><A HREF=' + str1 + '>number</A></TH>' +
'<TH><A HREF=' + str2 + '>text</A></TH>' +
'<TH><A HREF=' + str3 + '>date</A></TH>' +
'<TH><A HREF=' + str4 + '>name</A></TH>' +
'</TR>';
for (var i=0; i<length; i++)
output += '<TR>' +
'<TD>' + object[i].number+ '</TD>' +
'<TD>' + object[i].text + '</TD>' +
'<TD>' + object[i].date + '</TD>' +
'<TD>' + object[i].name + '</TD>' +
'</TR>';
output += '</TABLE>';
if (document.all){
document.all.tableDiv.innerHTML=output;
}
if (document.layers){
document.layers['tableDiv'].document.write(output);
document.layers['tableDiv'].document.close();
}
}

function myObjectBubbleSort(arrayName,length,property) {
if (property!="none"){
for (var i=0; i<(length-1); i++)
for (var j=i+1; j<length; j++)
if (sortMode=='desc'){
if (eval('arrayName[j].' + property + '<arrayName[i].' + property)) {
var dummy = arrayName[i];
arrayName[i] = arrayName[j];
arrayName[j] = dummy;
}
}else{
if (eval('arrayName[j].' + property + '>arrayName[i].' + property)) {
var dummy = arrayName[i];
arrayName[i] = arrayName[j];
arrayName[j] = dummy;
}
}
}
showObjectArray(myObjectArray,objectArrayIndex);
}

function init(){
myObjectArray[objectArrayIndex++] = new myObject(100,'abc','19971104','Me');
myObjectArray[objectArrayIndex++] = new myObject(400,'zzz','19961003','You');
myObjectArray[objectArrayIndex++] = new myObject(300,'fox','19950902','Them');
myObjectArray[objectArrayIndex++] = new myObject(50,'bad','19981201','Us');
myObjectBubbleSort(myObjectArray,objectArrayIndex,'none');
}
//--></SCRIPT>
</head>
<body onload="init();">
<div id="tableDiv" style="position:absolute; left:100; top:100"></div>
</body>
</html>

Give it a try - it should work on IE4+ and NN4+.

If you wanted to create your js array from a recordset and not hardcoded, you could now do this by changing the init() function. If you're using ASP you can probably use the Response.write statement to dynamically write out the init() javascript function to the page and hence as it is written, substitute your recordset values into the correct place in the js function. I can't write out the code for this as I'm very rusty with ASP! If you're not using ASP then I'm sure a similar principle would apply with whatever technology you're using.

Matt.

Chad,

Just been messing round with an ASP solution to your problem of dynamically creating the JS array. This VBscript seems to work -

<%
Dim objArr(2,3)

objArr(0,0)=100
objArr(0,1)="abc"
objArr(0,2)="19971104"
objArr(0,3)="Me"
objArr(1,0)=400
objArr(1,1)="zzz"
objArr(1,2)="19961003"
objArr(1,3)="You"
objArr(2,0)=300
objArr(2,1)="fox"
objArr(2,2)="19950902"
objArr(2,3)="Them"

Response.Write("<script language='javascript'>")
Response.Write("function init(){")
Dim i
for i=0 to Ubound(objArr)
Response.Write ("myObjectArray[objectArrayIndex++] = new myObject(" & objArr(i,0) & ",'" & objArr(i,1) & "','" & objArr(i,2) & "','" & objArr(i,3) & "');")
next
Response.Write("myObjectBubbleSort(myObjectArray,objectArrayIndex,'none');")
Response.Write("}</script>")
%>

Obviously I don't have a recordset, so I've still had to create a static array, but where I loop through my array you could loop through your recordset and should get similar results.

Good luck,
Matt.