SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Filtering mysql

  1. #1
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Filtering mysql

    I'm trying to make a filtering script that runs the various options on this page:

    http://www.stylianou.co.uk/designer-clothing/

    At the moment I have written lines of code for every possible combination of category / type / label which has proven a real headache.

    What I would like to know is how to write sql that returns all records if a field is empty. I've done this for a search thing on another site but this involves an integer, not text, in the column.

    On the other site the sql looked like this:

    if request("firstname") = "" then strFirstname = "?"
    if request("surname") = "" then strSurname = "?"
    if request("firstname") <> "" AND request("surname") <> "" then
    SET resultsRS = CONN.EXECUTE("SELECT * FROM user WHERE (user_firstname LIKE '%"&strFirstname&"%') AND (user_surname LIKE '%"&strSurname&"%')")


    I could use ? in the variables if they were empty.


    This doesn't work on the new site, I think because the variables are integers because they are links between tables.

    Here's an example of one of my many combinations:

    'designer clothing with TYPE (gender) AND CATEGORY AND LABEL selected
    ELSEIF strType <> "" AND strCategory <> "" AND strLabel <> "" THEN
    SET productRS = CONN.EXECUTE("SELECT * FROM product INNER JOIN label ON label_id = product_label_no INNER JOIN pic ON pic_product = product_id INNER JOIN type2 ON product_type_no = type2_id INNER JOIN item ON item_product = product_id WHERE item_qty > 0 AND product_type_no = "&strType&" AND product_category_no = "&strCategory&" AND product_label_no = "&strLabel&" GROUP BY product_id ORDER BY label_name, product_name LIMIT "&strId&","&strPage)


    Please help, does anyone know how I can do this?


    Jk

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by generaltomfooler View Post
    What I would like to know is how to write sql that returns all records if a field is empty.
    your question is interesting because it implies that you have some way of checking the contents of the form fields

    the method you are looking for goes like this: if a field is empty, leave that condition out of the SQL completely

    so if the TYPE and CATEGORY and LABEL fields have a value, then the SQL would look like this --
    Code:
    SELECT ... 
      FROM 
     WHERE product_type_no = "&strType&" 
       AND product_category_no = "&strCategory&" 
       AND product_label_no = "&strLabel&"
    but if CATEGORY field did not have a value, you just leave that condition out of the query, and it will return all categories (as long as the other conditions are met)
    Code:
    SELECT ... 
      FROM 
     WHERE product_type_no = "&strType&" 
       AND product_label_no = "&strLabel&"
    now, the major problem that people have with this strategy occurs when they have to try to figure out which conditions are actually going to be present, because of the WHERE keyword

    the first condition comes after the WHERE, and then additional conditions are appended with AND

    but the logic of deciding which one is first is what messes up people's application code

    here's the easy way: start the WHERE clause with WHERE 1=1

    then you can append any number of additional conditions with AND, and you don't have to use any logic to see which one comes first

    note that if the user doesn't fill in any fields, meaning that no additional conditions are appended, then the query essentially reduces to WHERE 1=1, which is true for all rows, and therefore is exactly what you want

    neat, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works! (Of course...)

    For others struggling with this problem, here's what I ended up with, hopefully it will be of some use:

    strCategory = request("category")
    strCategoryName = request("categoryname")
    IF strCategory = "" THEN
    strCategoryVar = "1"
    strCategoryField = "1"
    ELSE
    strCategoryVar = strCategory
    strCategoryField = "product_category_no"
    END IF

    strType = request("type")
    strTypeName = replace(request("typename"),"&","&amp;")
    IF strType= "" THEN
    strTypeVar = "1"
    strTypeField = "1"
    ELSE
    strTypeVar = strType
    strTypeField = "product_type_no"
    END IF

    strLabel = request("label")
    strLabelName = replace(request("labelname"),"&","&amp;")
    IF strLabel= "" THEN
    strLabelVar = "1"
    strLabelField = "1"
    ELSE
    strLabelVar = strLabel
    strLabelField = "product_label_no"
    END IF

    strMode = request("mode")
    strId = cint(request("id"))
    strPage = 17
    rsend = strId + strPage
    IF strId = "" then strId = 0
    SET productRS = CONN.EXECUTE("SELECT * FROM product INNER JOIN label ON label_id = product_label_no INNER JOIN pic ON pic_product = product_id INNER JOIN type2 ON product_type_no = type2_id INNER JOIN item ON item_product = product_id WHERE item_qty > 0 AND "&strTypeField&" = "&strTypeVar&" AND "&strCategoryField&" = "&strCategoryVar&" AND "&strLabelField&" = "&strLabelVar&" GROUP BY product_id ORDER BY label_name, product_name LIMIT "&strId&","&strPage)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    that's not quite what i meant, but it's very inventive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'd love to learn a better way if you can explain it?

    My thinking was to replace the column name with "1" and the variable name with "1" when the page didn't refer to the category etc. (and hence return all the records)


    Jk

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i already tried to explain it

    when a form field is missing, you omit the corresponding condition from the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Apr 2008
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was the problem, I had to write lots of options for all combinations of fields missing or not (about 15 different statements!)


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
  •