I'm using the following sql statement for a record set in a web page:
WHERE col11 ='colcheck' AND col2 LIKE '%colname%' or col1 LIKE '%colname%' or col3 LIKE '%colname%' or col4 LIKE '%colname%' or col5 LIKE '%colname%' or col6 LIKE '%colname%' or col7 LIKE '%colname%' or col8 LIKE '%colname%' or col10 LIKE '%colname%'
ORDER BY col2
It receives a value of 0 or 1 for the colcheck variable and can receive any input from the user for colname. It's just a simple search for contact info. The colcheck is whether or not the client is active. 1=active, 0=nonactive. Then the second colname entry allows the user to enter one search string that will search all the fields in the table. But when I execute the searches it doesn't seem to lock it down by the active/nonactive status. It seems to do the search on or and/or the other search variable. I thought the above code would lock it down to find a definite combinate of the two variables not just either/or.
I believe by 'lock down' he means it is not selecting based on the colcheck field. You will need to group your set of OR statements together using Paren () - the way you have it written the OR overrdes the AND.
Originally posted by Brett Kaiser
What does lock it down mean?
Also "%anything%' is bad...
I'm using the %anything% so that any field can be searched on in the table by only entering a search string in one place. Are there better ways around this other than creating a record set based off each searched field individually?
If you are going to be adding them to your chain of OR statements, yes, enclose in (). Any thing enclosed will be considered part of the 2nd element of the AND statement, and executed at the same time. Think back to Algebra and you should get all the logic you need.