SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    sql (mysql) question

    I'm using the following code:

    SELECT tbl3.col1, tbl3.col11, tbl3.col2, tbl3.col3, tbl3.col4, tbl3.col5, tbl3.col6, tbl3.col7, tbl3.col8, tbl3.col13
    FROM tbl3
    WHERE (tbl3.col13 = 1) AND (tbl3.col11 = colcheck) AND (tbl3.col2 LIKE '%colname%' OR tbl3.col3 LIKE '%colname%' OR tbl3.col4 LIKE '%coladdress%' OR tbl3.col5 LIKE '%coladdress%' OR tbl3.col6 LIKE '%colcity%' OR tbl3.col7 LIKE '%colstate%' OR tbl3.col8 LIKE '%colzip%')
    ORDER BY col2 ASC

    I have a form with contact info fields that the user can fill in. They only have to fill in whatever fields they want. col13 has to equal 1 all the time. col11 equals the value of a radio button. The rest of the fields are the addressing info. I want the user to be able to enter "CLEVE" in the city and field and it will select all the "CLEVELAND" records, or enter "MAIN" and get all the records that contain "MAIN" in the address field(like 101 Main St.). I don't want the user to have to enter all the fields, just the ones they want to search on. But my sql code seems to ignore everything other than the col13=1 and col11=colcheck. I'm new to this so my code could be far off. Anyone have any ideas?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,260
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    the reason it seems to ignore it is as follows

    suppose the user leaves the colcity form field empty

    then that part of the clause evaluates to tbl3.col6 LIKE '%%' which evaluates to true for every row

    so as long as even one field is left empty, the third of the three ANDed expressions is always true, which makes it useless

    make sense?

    what you want to do is not include a condition if that field is left empty

    this requires scripting logic to test the form fields

    i wrote an article about it:
    The "any" option in dynamic search SQL
    (site registration may be required, but it's free)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •