Sorting Fields From <cfoutput>

[scripter73]

Hi,

My goal is to allow user to click on a link (preferably the Field Name Header), and have that data "re-sorted" based on the user's request.

Is this something that can be done using Cold Fusion (without <cfgrid>)or will I have to resort to another tool like Javascript.

Example. My data will already be presented in a sorted format, but if the user wanted to change it to sort by date, etc.

Below is a sample of my table that I'd like to sort:


<!--- Beginning of Data Table --->
<table width=800 border=0>
<tr bgcolor=&quot;6699cc&quot;>
<td width=10><font size=3 color=black>Binder#</font></td>
<td width=10><font size=3 color=black>Tran Date</font></td>
<td width=10><font size=3 color=black>Agent Code</font></td>
<td width=40><font size=3 color=black>Insured Name</font></td>
<td width=10><font size=3 color=black>Account#</font></td>
<td width=200><font size=3 color=black>Bank Amount</font></td>
</tr>
<cfset #grand_total# = 0>
<cfoutput query=&quot;achdata&quot;>
<cfif (#achdata.currentrow# mod 2) eq 0>
<tr bgcolor=&quot;6699CC&quot; width=800>
<cfelse>
<tr bgcolor=&quot;ffffff&quot; width=800>
</cfif>
<td width=10>#wach_binder_number#</td>
<td width=10>#wach_tran_date#</td>
<td width=10>#wach_agent_code#</td>
<!--- If there's a middle initial, display it. If not, then don't display anything. --->
<cfif #wach_ins_middle_initial# neq &quot;&quot; and #wach_ins_middle_initial# neq &quot; &quot;>
<td width=40>#wach_ins_last_name#, #wach_ins_first_name#, #wach_ins_middle_initial#</td>
<cfelse>
<td width=40>#wach_ins_last_name#, #wach_ins_first_name#</td>
</cfif>
<td width=10>#wach_bank_account_num#</td>
<td width=200>#wach_bank_amount#</td>
<cfset #grand_total# = #grand_total# + #wach_bank_amount#>
</cfoutput>
</tr>
<tr width=800>
<td width=400></td>
<td width=400 align=&quot;right&quot; colspan=5><cfoutput>Grand Total is #grand_total#.</cfoutput></td>
</tr>
</table>

<!--- End of Data Table --->



Thanks in advance,
scripter73

 

[tlhawkins]

Hey scripter,

nice easy to read code.

I see 2 possiblities for you. If you want to avoid javascript then you will have to have a the re-sort buttons recall the script with a URL variable telling the page what to sort by, this will take a few seconds each time they resort as the page will have to reload. It would be simple to reword the Query to sort by whatever you wanted.

The Javascript way would be more dynamic and much faster. You would have to make a DIV or Layer to contain the data sorted all possible ways, then depending on the the column that is clicked a different layer would become visible. I will warn you that this can be done but it is a lot of work to make it work in IE and Netscape as they both do layers different, and it will require IE3.0+ ( Or something like that ) and I think Netscape 4.0+ . I admit I like the javascript method better but sometimes we have to worry about the user.

Have fun. Let me know if you need help implementing either of these methods.

 

[calista]

Well, scripter73, you've tackled a tough one. tlhawkins, it never occured to me to use JavaScript. Probably because I'm such a novice at it. I suspect that's the easiest solution, but I'll tell you briefly what I did, and if you want more details, let me know.

I am outputting the results of a query, and when I do the query, I sort on the column that is the default. I then read the query results into a two-dimensional array, sort the array, and display the contents of the array rather than the query. The headers in the table are links to the same page, and these links pass URL variables. At the top of the template, I check for the existance and value of the URL variables, and these go into my sort routine. There are, of course, default values for the first time through.

Clear as mud? Hope this helps! Calista :-X
Jedi Knight,
Champion of the Force

 

[scripter73]

Hi tlhawkins/calista,

Sorry it took me so long to respond. I appreciate all of your responses.

Here's what I have done so far:

- Built query data. (Pre-sorted by last name, date). Basically I have 5 columns (Cust#, Date, Name, Acct#, Amount). I want the user to be able to sort by date.
- Built array of query data(bankArray).
- Displayed data for users.

I just can't seem to figure out how to get started to re-sort the data that's in my array. I try using the ArraySort() feature, but I receive errors. I think its because you can only sort on one row at a time.

Here's a snippet of my code:

<!--- BEGINNING: ARRAY --->
<cfset bankArray = ArrayNew(2)>
<!--- Declare the array --->
<CFSET bankArray = ArrayNew(2)>
<!--- Populate the array row by row --->
<CFLOOP QUERY = &quot;achdata&quot;>
<CFSET bankArray[CurrentRow][1] = achdata.wach_binder_number[CurrentRow]>
<CFSET bankArray[CurrentRow][2] = achdata.wach_tran_date[CurrentRow]>

<!--- NAME MANIPULATION: If there's a middle initial, use it. If not, then don't use anything. --->
<cfif #achdata.wach_ins_middle_initial[CurrentRow]# neq &quot;&quot; and #achdata.wach_ins_middle_initial[CurrentRow]# neq &quot; &quot;>
<cfset #fullname# = &quot;<cfoutput>#achdata.wach_ins_last_name[CurrentRow]#, #achdata.wach_ins_first_name[CurrentRow]#, #achdata.wach_ins_middle_initial[CurrentRow]#</cfoutput>&quot;>
<cfelse>
<cfset #fullname# = &quot;<cfoutput>#achdata.wach_ins_last_name[CurrentRow]#, #achdata.wach_ins_first_name[CurrentRow]#&quot;>
</cfif>

<CFSET bankArray[CurrentRow][3] = #fullname#>
<CFSET bankArray[CurrentRow][4] = achdata.wach_bank_account_num[CurrentRow]>
<CFSET bankArray[CurrentRow][5] = achdata.wach_bank_amount[CurrentRow]>
</CFLOOP>
<!--- ENDING: ARRAY --->

<!--- ARRAY SORT --->
<cfset dateArray = ArraySort(bankArray[CurrentRow], &quot;numeric&quot;, &quot;asc&quot>


Can you point me in the right direction for the sorting? I was thinking about when a user clicks the header, I could just redo the query by re-submitting my array and changing the ORDER.

Any help is appreciated.

Thanks,
scripter73

 

[calista]

You're right, ArraySort() only works with one dimensional array. You got as far as getting your data into an array. What you need now is a two dimensional sort. There are a couple on Allaire's Developers' Exchange

http://devex.macromedia.com/developer/gallery/SearchResults.cfm?keywords=array+sort&x=12&y=9.

However, I finally broke down and wrote my own, because this one didn't work if two values you were sorting on were equal, and I don't think it worked on date/time objects, which I also wanted to do. Mine's a little more complicated to use, but I'll share it with you if you're interested. Calista :-X
Jedi Knight,
Champion of the Force

 

[Guest_imported]

Hey Calista,

I am certainly interested in your two-dimensional array sorting routine. Do you want to correcpond by email? I'm also on AIM as (you guessed it) a440guy.

 

[calista]

Email's fine.

dsprang@tollgrade.com Calista :-X
Jedi Knight,
Champion of the Force