SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    filter based on selection, which may be "all" (was "help with SQL")

    i have a table with following columns:
    name, city, state, country.

    i want to be able to filter data based on the user's selections. that is name of the person, city, state, country. (city, state, country are menus/lists containing "ALL" option as the first option meaning they some may be selected as "ALL"). also 'name' is a text field and it may be empty. How can I perform this filter. I desperatly need help!.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the idea is to generate a WHERE condition whenever there is a filter condition, and not generate a WHERE condition when there isn't

    in psuedocode --
    Code:
    select foo, bar
      from table
     where 1=1
    if city_param <> "all"
       and city = 'city_param'
    endif
    if state_param <> "all"
       and city = 'state_param'
    endif
    ...
    the reason for starting the WHERE clause with 1=1 is so that if there are no conditions at all, then all rows will be returned (since 1=1 is true for all rows)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    personallly, i prefer 937=937, but it's all just a matter of style.

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    can you guys write the SQL code for me please

    i can't grasp this. i guess i'm stupid. can someone please write the actual SQL statement that i would use??

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the actual SQL is just like it is in post #2

    except you'd use your actual column names instead of foo and bar (these are mere syntactic placeholders)

    the stuff in red, of course, isn't part of the SQL, it merely governs whether that particular AND condition is actually incorporated into the SQL or not

    if the dropdown parameter is "all", don't generate the condition

    similarly for the text field, if it's missing (zero length) then don't generate the condition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard Ren's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    1,060
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Occasionally use a COALESCE hack, if don't want to dynamically generate SQL.

    Code:
    SELECT foo, bar FROM table
    WHERE COALESCE(:cityParam, city) = city
    AND COALESCE(:stateParam, state) = state
    AND COALESCE(:countryParam, country) = country
    so if any of the parameters are NULL they wont filter.

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2005
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    great, that helped-i finally understand it. works great

    Yea!


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
  •