Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multi value search

    HI,

    I have a three drop-down menus for City, Area, Zip.

    I am using 'like' in my search query which is:

    select col_nam from table where city like %txtcity% AND area like %txtarea% AND zip like %txtzip%

    But, since AND is being used, if any of the parameter is NOT given, the result is Zero and If I use OR instead, it still not work if only two values are selected. For example,

    If a user has selected city: A, area: X , then query using OR will return city matching A, area matching X BUT it will also return the area which should NOT be included.


    So, what should be done here. Do you think that 'like' should be used here ?
    Or any other suggestion?


    Thanks
    Yasir

  • #2
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    You have to construct the SQL dynamically.

    i.e.

    sql = "select col_nam from table where 1=1 "; // so we can just use AND
    if ( txtcity != null ) {
    sql += "and city like '%" + txtcity + "%' ";
    }
    if ( txtArea != null ) {
    sql += "and area like '%" + txtarea + "%' ";
    }
    if ( txtZip != null ) {
    sql += "and zip like '%" + txtzip + "%' ";
    }

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #3
    New Coder
    Join Date
    Aug 2005
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use dynamic SQL in combination with dynamic Command Parameters for better application security.
    Robert
    Gee! Web Tools
    Web Content Management / Internet Marketing modules including:
    Page Editor - Calendar Manager - News Editor - Contact Organizer - Mail Wizard


  •  

    Posting Permissions

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