Professional Web Applications Themes

Dynamic Filtering? - Coldfusion - Advanced Techniques

Maybe something like this? SELECT your_columns FROM your_table <cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif>...

Sponsored Links
  1. #1

    Default Re: Dynamic Filtering?

    Maybe something like this?

    SELECT your_columns
    FROM your_table
    <cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif>
    Sponsored Links
    paross1 Guest

  2. #2

    Default Re: Dynamic Filtering?

    Hi Paross1,

    Thanks for the reply. I'll give it a try. Is the idea that only the IF
    statements that evaluate true will be executed? What about stringing together
    the IF statements that return true? For example:

    SELECT your_columns
    FROM your_table
    <cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif>
    <cfif selected_country NEQ 0>WHERE Country_ID = #selected_country # </cfif>
    <cfif selected_region NEQ 0>WHERE Region_ID = #selected_region # </cfif>

    or would I write
    SELECT your_columns
    FROM your_table
    <cfif selected_state NEQ 0>WHERE State_ID = #selected_state # </cfif> AND
    <cfif selected_country NEQ 0>WHERE Country_ID = #selected_country # </cfif> AND
    <cfif selected_region NEQ 0>WHERE Region_ID = #selected_region # </cfif>

    Thanks again.


    Novian Guest

  3. #3

    Default Re: Dynamic Filtering?

    You can structure the where clause as follows to make the 'ands' easier

    SELECT your_columns
    FROM your_table
    Where 1=1
    <cfif #selected_state# NEQ 0>and State_ID = #selected_state # </cfif>
    <cfif #selected_country# NEQ 0>and Country_ID = #selected_country # </cfif>
    <cfif #selected_region# NEQ 0>and Region_ID = #selected_region # </cfif>

    the 1=1 is always true, so any number of 'anded' where clauses can be added
    after that

    This way the 'ALL' selection can be used in any of the fields and the query
    will return the correct result

    rmaglies Guest

  4. #4

    Default Re: Dynamic Filtering?

    Originally posted by: rmaglies
    You can structure the where clause as follows to make the 'ands' easier

    SELECT your_columns
    FROM your_table
    Where 1=1
    <cfif #selected_state# NEQ 0>and State_ID = #selected_state # </cfif>
    <cfif #selected_country# NEQ 0>and Country_ID = #selected_country # </cfif>
    <cfif #selected_region# NEQ 0>and Region_ID = #selected_region # </cfif>

    the 1=1 is always true, so any number of 'anded' where clauses can be added
    after that

    This way the 'ALL' selection can be used in any of the fields and the query
    will return the correct result

    Hey rmaglies,

    Yeah, that's what I ended up doing although I didn't use the 'Where 1=1'. Is
    this necessary? If so, why?

    I really appreciate your input as well as that of paross1. I've been learning
    so much via members like you and books from guys like Ben Forta.

    Thanks again.

    Novian

    Novian Guest

  5. #5

    Default Re: Dynamic Filtering?

    Look at what happens if you don't include WHERE 1 = 1 and all three of your
    variables happen to equal 0 at the same time. You would have a query with an
    empty WHERE clause, which will throw a database error. Including the 1=1 it
    causes two behaviors, it prevents an error if your parameters are all 0, and
    your query returns all rows (kind of a default ALL).

    Phil

    paross1 Guest

Similar Threads

  1. Filtering & displaying dynamic information
    By Gabe the Animator in forum Macromedia Flash Data Integration
    Replies: 3
    Last Post: January 15th, 07:05 PM
  2. advanced filtering
    By Billium99 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: March 9th, 11:57 PM
  3. Row filtering
    By siaj in forum ASP.NET Web Services
    Replies: 0
    Last Post: February 24th, 06:33 PM
  4. Filtering
    By Brad M. in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 12th, 12:46 AM
  5. HttpModule for ASP and ASP.NET URL filtering
    By Jon Sequeira in forum ASP.NET General
    Replies: 3
    Last Post: June 26th, 03:36 PM

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