Skip to main content
J
jlig
Inspiring
September 4, 2015
Answered

Sorting by database field when using cfdirectory

  • September 4, 2015
  • 1 reply
  • 1321 views

I have the following working code but need to sort the list by a field (sortID) from my database (not a value from the directory)

- How do I add this?

- I tried adding "sortID" in below, but it doesn't have any effect?

- sortID is a 3 digit varchar field in MySQL

<div data-role="collapsible" data-collapsed="true">


    <h3 align="center">Board</h3>


    <ul data-role="listview" data-theme="d" data-inset="true">


      <cfset DirList = "C:\Inetpub\wwwroot\CRM\CCC\attachments\board\">


      <cfset site = ("https://crm.domain.com/crm/ccc/attachments/board/")>


      <cfdirectory directory="#DirList#" name="qDir" action="list" sort="sortID DESC">


      <cfquery dbtype="query" name="dirsOtherOnly">


  SELECT * FROM qDir ORDER BY sortID DESC


  </cfquery>


      <cfoutput>


        <cfloop query="dirsOtherOnly">


          <li>


          <a href="#site##dirsOtherOnly.name#" title="Click to Preview" target="_blank">#dirsOtherOnly.Name#</a>


        </li></cfloop>


     


      </cfoutput>


    </ul>


    <cfif ("sicr" NEQ "region6" AND #Session.kt_login_user# EQ "john" OR #Session.kt_login_user# EQ "bob")>


      <p align="left">Click to add attachment: <cfoutput><a href="ul_l.cfm?id2=500&id5=Board" data-transition="fade" target="_blank">Attachments</a></cfoutput></p>


    </cfif>


  </div>
This topic has been closed for replies.
Correct answer BKBK

Why would you want to order the result-set by a column that apparently has nothing to do with it? In any case, one solution is to get the sortID column from the database, and append it to the quey resulting from cfdirectory

Something along these lines

<cfdirectory directory="#DirList#" name="qDir" action="list">

<!--- Get sortID from database --->

<cfquery name="getSortID" datasource="yourDSN">

    SELECT sortID

    FROM TBL

</cfquery>

<!--- Create sortID array. --->

<cfset sortIdArray = ArrayNew(1)>

<cfset i = 1>

<cfoutput query="getSortID">

    <cfset sortIdArray = sortId>

    <cfset i = i+1>

</cfloop>

<!--- Use the array to add the sortID column to the qDir query. --->

<cfset nColNumber = QueryAddColumn(qDir, "sortID", "VarChar",sortIdArray)>

<!--- SortID is now a column of qDir. You may therefore use it in the order-by clause --->

<cfquery dbtype="query" name="dirsOtherOnly"> 

SELECT * FROM qDir

ORDER BY sortID DESC 

</cfquery>

1 reply

BKBK
Community Expert
BKBKCommunity ExpertCorrect answer
Community Expert
September 5, 2015

Why would you want to order the result-set by a column that apparently has nothing to do with it? In any case, one solution is to get the sortID column from the database, and append it to the quey resulting from cfdirectory

Something along these lines

<cfdirectory directory="#DirList#" name="qDir" action="list">

<!--- Get sortID from database --->

<cfquery name="getSortID" datasource="yourDSN">

    SELECT sortID

    FROM TBL

</cfquery>

<!--- Create sortID array. --->

<cfset sortIdArray = ArrayNew(1)>

<cfset i = 1>

<cfoutput query="getSortID">

    <cfset sortIdArray = sortId>

    <cfset i = i+1>

</cfloop>

<!--- Use the array to add the sortID column to the qDir query. --->

<cfset nColNumber = QueryAddColumn(qDir, "sortID", "VarChar",sortIdArray)>

<!--- SortID is now a column of qDir. You may therefore use it in the order-by clause --->

<cfquery dbtype="query" name="dirsOtherOnly"> 

SELECT * FROM qDir

ORDER BY sortID DESC 

</cfquery>

J
jligAuthor
Inspiring
September 8, 2015

BKBK, thanks for the help on this.
Your code worked great except that I had to change the cfoutput to cfloop:

ps: I'm storing the document details in a database, then displaying in a jquery mobile list, and needed the files sorted by sortID.
I allow the end user to change the sortID value as needed to sort the file list.

Thanks again.

J
jligAuthor
Inspiring
September 21, 2015

Quick question: the sort is correct the first time the page loads, but after a new doc is uploaded, the sort is out of whack? Any ideas? Is this a caching issue?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices