CF - multiple column sort

JohnMac328
JohnMac328 used Ask the Experts™
on
I am converting a program from ASP.  There is a print directory page that gives a directory in a printer friendly layout.  The data is pulled from a table and the first half of the page is filled and then it continues to the next half of the page.  I have duplicated that much in CF but I can't get the second set of data to sort correctly with the first set.  I am having to use an Access database. I have posted what I have so far.  Any help is appreciated.
<CFIF IsDefined("url.SortBy")>
<cfquery name="GetEmployees30" datasource="USGI_Data">
SELECT TOP 50 * FROM Directory_List ORDER BY #url.SortBy#
</cfquery>
<CFELSE>
<cfquery name="GetEmployees30" datasource="USGI_Data">
SELECT TOP 50 * FROM Directory_List ORDER BY last_name ASC 
</cfquery>
</cfif>

<cfquery name="GetEmployees60" datasource="USGI_Data">
select top 40 * from Directory_list
where id not in (
select top 50 Id from Directory_list) 
</cfquery>

<body>
<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=0 VALIGN=TOP>
<TR><TD COLSPAN=2>
<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=2 BGCOLOR=#000000>
<TR><TD ALIGN=CENTER BGCOLOR=#000000>Employee Phone List with ID's</TD></TR></TABLE></TD></TR>

<TR VALIGN=TOP><TD WIDTH=50% VALIGN=TOP>
<TABLE WIDTH=100% BORDER=0 CELLSPACING=0 CELLPADDING=0 ALIGN=TOP>
<TR><TD WIDTH=30% BGCOLOR=#999999></TD><TD WIDTH=15% BGCOLOR=#999999></TD>
<TD WIDTH=20% BGCOLOR=#999999>ID</TD><TD WIDTH=10% BGCOLOR=#999999>Ext</TD>
<TD WIDTH=25% BGCOLOR=#999999>Department</TD></TR>
<cfoutput query="GetEmployees30">
<TD WIDTH=30% ALIGN=left>#last_name#</TD>
<TD WIDTH=15% ALIGN=left>#first_name#</TD>
<TD WIDTH=20% ALIGN=left>#email_name#</TD>
<TD WIDTH=10% ALIGN=left>#phone_ext#</TD>
<TD WIDTH=25% ALIGN=left>#main_dept#</TD>	
</TR>
</cfoutput>
</TABLE>
</TD>
<TD VALIGN=TOP>
<TABLE WIDTH=100% VALIGN=TOP BORDER=0 CELLSPACING=0 CELLPADDING=0 ALIGN=TOP>
<TR><TD WIDTH=30% BGCOLOR=#999999>Name</TD>
<TD WIDTH=15% BGCOLOR=#999999>&nbsp;</TD>
<TD WIDTH=20% BGCOLOR=#999999>ID</TD>
<TD WIDTH=10% BGCOLOR=#999999>Ext</TD>
<TD WIDTH=25% BGCOLOR=#999999>Department</TD></TR>
<cfoutput query="GetEmployees60">
<TD WIDTH=30% ALIGN=left>#last_name#</TD>
<TD WIDTH=15% ALIGN=left>#first_name#</TD>
<TD WIDTH=20% ALIGN=left>#email_name#</TD>
<TD WIDTH=10% ALIGN=left>#phone_ext#</TD>
<TD WIDTH=25% ALIGN=left>#main_dept#</TD>	
</TR>
</cfoutput>
</TABLE></TD></TR>
</TABLE>

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015

Commented:
>> SELECT TOP 50   (show 50 rows)
>> select top 40      (show next 40 rows ...)

First, are those numbers really hard coded?  If the page always shows the TOP 90 records, just pull all 90 in one query. Then and use startrow/maxrows to control how they're displayed.


>> second set of data to sort correctly with the first set.

   Probably because the 1st query is ORDER(ed) BY something

     ie  ORDER BY #url.SortBy#  OR  ORDER BY last_name ASC

   ... and the 2nd query isn't.  So they're not pulling the records in  the same order.  

Author

Commented:
Hi agx,

Looks like I should use the startrow/maxrow, do you have a syntax example where it would fit into this mess?
Most Valuable Expert 2015

Commented:
Not tested, but something like

<cfquery name="GetAllEmployees">
SELECT TOP 90  * .....
</cfquery>

<cfoutput query="GetEmployees"  startRow="1" maxrows="50">
.... html stuff ...
</cfoutput>
... more html ...
<cfoutput query="GetEmployees"  startRow="51" maxrows="40">
.... html stuff ...
</cfoutput>


Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Most Valuable Expert 2015
Commented:
ie  Run 1 query only and use
<cfoutput query="GetEmployees"  startRow="1" maxrows="50">
... instead of
<cfoutput query="GetEmployees30">

and this
     <cfoutput query="GetEmployees"  startRow="51" maxrows="40">
... instead of
      <cfoutput query="GetEmployees60">
Most Valuable Expert 2015

Commented:
>> <CFIF IsDefined("url.SortBy")>
>>       <cfquery>...   </cfquery>
>> <CFELSE>
>>       <cfquery>...   </cfquery>
>> </CFIF>

Also, you don't need 2 queries.  Just use CFPARAM to set a default.

     <cfparam name="url.SortBy" default="Last_name">
     <cfquery ..> SELECT TOP 90 * FROM Directory_List ORDER BY #url.SortBy#</cfquery>

BUT ... that's dangerous code.  Though MS Access doesn't support multiple statements,
that query is a big sql injection risk w/other db types.  So you might want to scrub the data. Otherwise, bad things may happen if you ever upgrade to MS SQL, etc..

Author

Commented:
Perfect - thanks again agx - hope you are happy with Expert Exchange :)
Most Valuable Expert 2015

Commented:
You're welcome, and I love EE (where I get to hang out with the rest of the CF geeks :)

Author

Commented:
That's great news

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial