sql (mysql) question

[johnjohn2]

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?

[r937]

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)