Professional Web Applications Themes

output of a <cfquery> - Macromedia ColdFusion

I have a pretty straightforward query - please see code attached. The parameters passed to query are - ('55293583-B744-11D5-AF4F0002A5070708','54E68426-FD6C-8626-BA1B43356E37A64E','B9 003BB7-5DEE-11D6-AF780002A5070708','552936AD-B744-11D5-AF4F0002A5070708','C24C0C 37-D64C-B692-84EF1BFC6E4180B3') The order of the parameters is the order in which the user wants the items to be displayed. My problem here is that when outputting the query they are automatically ordered by objectid and so they show up as- 54E68426-FD6C-8626-BA1B43356E37A64E 55293583-B744-11D5-AF4F0002A5070708 552936AD-B744-11D5-AF4F0002A5070708 B9003BB7-5DEE-11D6-AF780002A5070708 C24C0C37-D64C-B692-84EF1BFC6E4180B3 There isn't any way that I could order them by in the query. The createdDateTime is one field that I can use, but then i would have to create the items in the same fashion ...

  1. #1

    Default output of a <cfquery>

    I have a pretty straightforward query - please see code attached.

    The parameters passed to query are -
    ('55293583-B744-11D5-AF4F0002A5070708','54E68426-FD6C-8626-BA1B43356E37A64E','B9
    003BB7-5DEE-11D6-AF780002A5070708','552936AD-B744-11D5-AF4F0002A5070708','C24C0C
    37-D64C-B692-84EF1BFC6E4180B3')

    The order of the parameters is the order in which the user wants the items to
    be displayed. My problem here is that when outputting the query they are
    automatically ordered by objectid and so they show up as-

    54E68426-FD6C-8626-BA1B43356E37A64E
    55293583-B744-11D5-AF4F0002A5070708
    552936AD-B744-11D5-AF4F0002A5070708
    B9003BB7-5DEE-11D6-AF780002A5070708
    C24C0C37-D64C-B692-84EF1BFC6E4180B3

    There isn't any way that I could order them by in the query. The
    createdDateTime is one field that I can use, but then i would have to create
    the items in the same fashion i want them to display and is a pain.

    I'm sure there is a way to programattically control the display based on the
    order it is fed to the query. Any thoughts on this will be much appreciated.

    Thanks a bunch.




    select o.*
    from objects o
    where o.objectid IN
    ('55293583-B744-11D5-AF4F0002A5070708','54E68426-FD6C-8626-BA1B43356E37A64E','B9
    003BB7-5DEE-11D6-AF780002A5070708','552936AD-B744-11D5-AF4F0002A5070708','C24C0C
    37-D64C-B692-84EF1BFC6E4180B3')

    cfcypher Guest

  2. #2

    Default Re: output of a <cfquery>

    Instead of battling at the database level try getting this done at the
    output/display level.

    <cfset myorder =
    "55293583-B744-11D5-AF4F0002A5070708,54E68426-FD6C-8626-BA1B43356E37A64E,B9003BB
    7-5DEE-11D6-AF780002A5070708,552936AD-B744-11D5-AF4F0002A5070708,C24C0C37-D64C-B
    692-84EF1BFC6E4180B3">

    <cfloop from="1" to="#listlen(myorder)#" index="lstItm">
    <cfloop query="myobjects">
    <cfif myobjects.classID eq lstItm>#myobjects.classID#</cfif><br>
    </cfloop>
    </cfloop>

    What that code does is.....
    1. it puts the order in which you want your output into a comma delimited
    list. Note that each value is not quoted like you had.

    2. We loop over the list.

    3. For each time we loop over the list, we loop over our resultset and see if
    the "currentrow" has the same value as that of our list. If yes we output.

    hope this helps.

    revblont Guest

  3. #3

    Default Re: output of a <cfquery>

    You can use DECODE function:

    SELECT o.* ,
    DECODE(o.objectid , '55293583-B744-11D5-AF4F0002A5070708', 1,
    '54E68426-FD6C-8626-BA1B43356E37A64E', 2,
    'B9003BB7-5DEE-11D6-AF780002A5070708', 3,
    '552936AD-B744-11D5-AF4F0002A5070708', 4,
    'C24C0C37-D64C-B692-84EF1BFC6E4180B3', 5) sort_order
    FROM objects o
    where o.objectid IN
    ('55293583-B744-11D5-AF4F0002A5070708','54E68426-FD6C-8626-BA1B43356E37A64E','B9
    003BB7-5DEE-11D6-AF780002A5070708','552936AD-B744-11D5-AF4F0002A5070708','C24C0C
    37-D64C-B692-84EF1BFC6E4180B3')
    ORDER BY sort_order




    CF_Oracle Guest

  4. #4

    Default Re: output of a <cfquery>

    Only thing that i changed in your code was that instead of-
    <cfloop from="1" to="#listlen(myorder)#" index="lstItm">

    I used
    <cfloop from="1" to="#myorder#" index="lstItm">

    Because, according to your code it will evaluate the length against the ID and
    it would never work.
    Thanks for the tip.


    Originally posted by: revblont
    Instead of battling at the database level try getting this done at the
    output/display level.

    <cfset myorder =
    "55293583-B744-11D5-AF4F0002A5070708,54E68426-FD6C-8626-BA1B43356E37A64E,B9003BB
    7-5DEE-11D6-AF780002A5070708,552936AD-B744-11D5-AF4F0002A5070708,C24C0C37-D64C-B
    692-84EF1BFC6E4180B3">

    <cfloop from="1" to="#listlen(myorder)#" index="lstItm">
    <cfloop query="myobjects">
    <cfif myobjects.classID eq lstItm>#myobjects.classID#</cfif><br>
    </cfloop>
    </cfloop>

    What that code does is.....
    1. it puts the order in which you want your output into a comma delimited
    list. Note that each value is not quoted like you had.

    2. We loop over the list.

    3. For each time we loop over the list, we loop over our resultset and see if
    the "currentrow" has the same value as that of our list. If yes we output.

    hope this helps.



    cfcypher Guest

Similar Threads

  1. Output from different datasource in cfquery statement
    By Ryunosuke in forum Coldfusion Server Administration
    Replies: 3
    Last Post: February 5th, 10:24 PM
  2. cfquery output duplicatig a cfset variable
    By TimMcGeary in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 16th, 08:31 PM
  3. Output from Cfquery
    By mike in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 29th, 04:52 PM
  4. Replies: 1
    Last Post: October 10th, 06:11 PM
  5. Replies: 0
    Last Post: August 20th, 01:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139