Search code examples
coldfusioncoldfusion-9

SQL Where between word value ranges (eg., "low" to "high")

I have a field in my database that has 5 possible values: fair, good, very good, ideal, siganture ideal

I have a coldfusion form that has 2 drop-downs each with all the values. What I am looking to do is be able to have the user select a range. For example dropdown1 = Fair dropdown2 = Very Good. So this would somehow generate the SQL WHERE statement:

grade IN ('fair', 'good', 'very good')

Can you think of a smart way to program this given that the values have to be this way. I think maybe if I put them in an array and then looped through it or something. I'm a little stumped on this any help would be appreciated.

Solution

  • As others mentioned, redesigning is ultimately the better course of action, both in terms of efficiency and data integrity. However, if you absolutely cannot change the structure, a possible workaround is to create a lookup table of the allowable grade descriptions, along with a numeric rating value for each one:

     GradeID | GradeText        | Rating
 1       | Fair             | 0
 2       | Good             | 1
 3       | Very Good        | 2
 4       | Ideal            | 3
 5       | Signature Ideal  | 4

    Then populate your select list from a query on the lookup table. Be sure to ORDER BY Rating ASC and use the rating number as the list value. Then on your action page, use the selected values to filter by range. (Obviously validate the selected range is valid as well)

      SELECT  t.ColumnName1, t.ColumnName2
  FROM    SomeTable t INNER JOIN YourLookupTable lt ON lt.Grade = t.GradeText
  WHERE   lt.Rating BETWEEN <cfqueryparam value="#form.dropdown1#" cfsqltype="cf_sql_integer">
          AND <cfqueryparam value="#form.dropdown2#" cfsqltype="cf_sql_integer">

    Again, I would recommend restructuring instead. However, the above should work if that is really not an option.