Professional Web Applications Themes

One Search String/Multiple Fields - Coldfusion Database Access

Hello! What I am trying to do is create a query page for my users that allows them to enter one search string (var=#string#) and then click checkboxes that correspond to the field values that they want to search for the string in (var=#paramSelect#). Unfortunately I haven't been able to make this work correctly and was hoping that someone here might be able to help me with this. So far I have tried the following methods: My initial method: SELECT.... FROM.... WHERE #paramSelect# LIKE '%#String#%' The problem here is that when the query is executed, it is sent to the ...

  1. #1

    Default One Search String/Multiple Fields

    Hello! What I am trying to do is create a query page for my users that allows
    them to enter one search string (var=#string#) and then click checkboxes that
    correspond to the field values that they want to search for the string in
    (var=#paramSelect#). Unfortunately I haven't been able to make this work
    correctly and was hoping that someone here might be able to help me with this.
    So far I have tried the following methods:

    My initial method:
    SELECT....
    FROM....
    WHERE #paramSelect# LIKE '%#String#%'

    The problem here is that when the query is executed, it is sent to the server
    looking like this:

    SELECT....
    FROM....
    WHERE paramSelect1,paramSelect2,paramSelectn LIKE '%"String"%'

    Which causes SQL to throw an error because the values in #paramSelect# are
    being returned as a list.
    What I want it to look like upon execution is:

    ....
    WHERE paramSelect1 LIKE '%#String#%' OR paramSelect2 LIKE '%#String#%' OR
    paramSelect3 LIKE '%#String#%'

    I have also tried the following method:
    ....
    <cfif FORM.paramSelect IS NOT ""></cfif>
    WHERE 1=1
    <cfloop list="#FORM.paramSelect#" delimiters="," index="i">
    AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">
    LIKE '%#FORM.String#%'
    </cfloop>

    But this method returns no records even when I search on only one field.
    Does anyone have any ideas of how to do this other than having to use a lot of
    <cfif> tags and separate variables like:

    WHERE 0=0
    <cfif FORM.Taxon IS NOT "">
    AND Taxon LIKE '%#Taxon#%'
    </cfif>
    <cfif FORM.Family IS NOT "">
    AND Family LIKE '%#Family#%'
    </cfif>
    <cfif FORM.Country IS NOT "">
    AND Country LIKE '%#Country#%'
    </cfif>
    <cfif FORM.State IS NOT "">
    AND State LIKE '%#State#%'
    </cfif>
    <cfif FORM.County IS NOT "">
    AND County LIKE '%#County#%'
    </cfif>

    Thank you for any input!

    KYMatt Guest

  2. #2

    Default Re: One Search String/Multiple Fields

    In your last method, change the ands to ors. Add brackets. The final results
    should resemble:

    where 0 = 0
    and
    (
    field1 like '%#string#%'
    or field2 like '%#string#%'
    etc
    )

    Expect it to be slow.

    Dan Bracuk Guest

  3. #3

    Default Re: One Search String/Multiple Fields

    Dan, thank you for your reply, a couple of questions though: If I use the
    method that you are suggesting won't I be eliminating the capability of users
    to define which columns they want to search for the string in though? By
    looking at your code example it seems that I am telling SQL to search each
    column for '%#String#%', when I want it to search only the user-selected
    columns for '%#String#%' which will be different for each user. You are
    correct in the fact that I would like to change the ANDs to ORs, but my problem
    with this has been that my statement gets rejected when someone chooses say
    field3 and field4 to search on, resulting in a query looking like:

    WHERE OR field3 LIKE '%#String#%' OR field4 LIKE '%#String#%'

    which is causing an error because it should look like:

    WHERE field3 LIKE '%#String#%' OR field4 LIKE '%#String#%'

    KYMatt Guest

  4. #4

    Default Re: One Search String/Multiple Fields

    What happened to the where 0 = 0 part?


    Dan Bracuk Guest

  5. #5

    Default Re: One Search String/Multiple Fields

    Dan, I don't think that I am being clear here (or I have completely
    misinterpreted what you are trying to say). The problem that I am trying to
    overcome right now is having to put each field into the statement on my results
    page. Currently the question of using AND or OR is not a big problem for me.
    What I DO want to do is pass the variable #paramSelect# which contains the
    values from each of the checked checkboxes from my search page. These values
    are the field names in my DB. When a user selects a checkbox, the value of
    that checkbox is being passed to my output via #paramSelect#, so if one
    checkbox is checked then I get #paramSelect# equal to field1. If two or more
    checkboxes are checked by the user then the value of #paramSelect# becomes a
    list of the values (i.e. field1,field3,field5) - this is the dynamic part that
    I am having trouble with. With your suggestion what I am doing is hard-coding
    each of the field values into my results page:

    AND
    (
    form.field1 LIKE '%#String#%'
    OR form.field2 ....
    )
    )

    when what I want to do is have the values passed from the #paramSelect#
    variable, separated from the list, and dynamically inserted into the code. For
    example:

    say that #paramSelect# is passed and contains the
    values field1,field3,field5
    I would like to write a loop that will separate
    each value contained in #paramSelect#
    i.e.(field1) (field3) (field5)
    and then build the WHERE from these, allowing me
    to place something like my previous example:

    <cfif FORM.paramSelect IS NOT
    ""></cfif>
    WHERE 1=1
    <cfloop list="#FORM.paramSelect#"
    delimiters="," index="i">
    AND <cfqueryparam cfsqltype="cf_sql_varchar"
    value="'#i#'">
    LIKE '%#FORM.String#%'
    </cfloop>

    The idea when I built that statement was that the values would be separated
    when the delimiter was reached (,) stored in the variable #i# and it would loop
    through the list contained within #paramSelect#. Unfortunately it doesn't seem
    to be doing this because I get 0 records returned even when I search on just 1
    field. My main reason for trying to do it this way is the fact that this DB is
    going to grow and the available fields are going to change. If I can store all
    of this in a variable and pass that to both the SELECT, WHERE, and a dynamic
    output table, then it relieves a lot of headaches and time going through and
    changing code in the future. Currently I have the dynamic SELECT and output
    tables built, but the WHERE part of the SQL statement is causing problems.

    Thanks again for your input!

    KYMatt Guest

  6. #6

    Default Re: One Search String/Multiple Fields

    So paramselect is either not defined, or a list?
    And you know how to loop through a list?
    And you know what you want to do if it's not defined?

    If you answer yes to all three questions, what's the part you are having
    trouble with? By the way, a list can contain only one element and still be
    considered a list.

    Dan Bracuk Guest

  7. #7

    Default Re: One Search String/Multiple Fields

    This might help too...
    When using:

    <cfif FORM.paramSelect IS NOT ""></cfif>
    WHERE 1=1
    <cfloop list="#FORM.paramSelect#" delimiters="," index="i">
    AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">
    LIKE '%#FORM.String#%'
    </cfloop>

    and looking at the dumped variables, my statement is being processed as ('ken'
    was the search string variable passed):

    WHERE 0=0 AND ? LIKE '%ken%' AND ? LIKE '%ken%' AND ? LIKE '%ken%'

    and my field values from #paramSelect# are reported as SQLPARAMETERS and
    listed in an array


    KYMatt Guest

  8. #8

    Default Re: One Search String/Multiple Fields

    Alright....I just finished filling out my application for the "village idiot"
    job - I think I have this one locked up! My problem was with this code:

    AND <cfqueryparam cfsqltype="cf_sql_varchar" value="'#i#'">

    which I changed to this

    AND #i#

    Sometimes the best answers are the easiest. Thanks for your help on this Dan
    and I apologize for not trying something so simple sooner.

    KYMatt Guest

Similar Threads

  1. PHP MySQL search same name in multiple dbase fields
    By mduke in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 28th, 09:14 PM
  2. building dynamic SQL string from multiple search values
    By Steven Scaife in forum ASP Database
    Replies: 1
    Last Post: August 12th, 11:08 AM
  3. Search multiple keywords across multiple fields
    By Darren Heinrich in forum ASP Database
    Replies: 3
    Last Post: February 6th, 05:29 AM
  4. SQL Search Multiple Fields ??
    By Ulf Wendel in forum PHP Development
    Replies: 5
    Last Post: July 5th, 02:22 PM
  5. Access Combo Box search allowing seacr on multiple fields
    By L Balogh in forum Microsoft Access
    Replies: 2
    Last Post: June 30th, 11:15 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