[RESOLVED] Searching for: "query" OR "query" AND ...

scraff

Hi everyone,

Just looking for some advice on a little problem im having. Im currently trying to build a search page to run more effectively AND efficiently.

Is there any way of searching for something with an OR in it without having to repeat the ANDs in the query?

EG:

$query = "SELECT * FROM products WHERE title LIKE '%$search_1%' AND expired = '0' OR title LIKE '%$search_2%' AND expired = '0'";

instead have something like:

$query = "SELECT * FROM products WHERE (title LIKE '%$search_1%' OR title LIKE '%$search_2%' ) AND expired = '0' ";

Is there anyway of doing this? As this doesnt seem to work

I have some big search queries that include a lot of ANDs and done want to keep repeating the whole query. Thanks!

johanafm

The reason the first doesn't work is because of operator precedence, if we consider AND and OR to be operators. It is the exact same thing as would happen with addition and multiplication

search_1 * expired + search_2 * expired

And the solution is, just as with add / mult to add parentheses to change the meaning

WHERE expired = 0 AND (title = 'this' OR title = 'that')

Also note that, assuming expired is of type INT, you really should have

expired = 0

rather than

expired = '0'

Bonesnap

If you're finding your queries are becoming quite large with lots of WHERE conditions, there are two things you can do to make your life a little easier.

First is to write your queries on multiple lines and use indentation if needed. A general rule of thumb that I learned is to begin each line with a MySQL keyword. For example, your second query:

$query = "SELECT * 
        FROM products 
        WHERE 
            (title LIKE '%$search_1%' 
            OR title LIKE '%$search_2%' ) 
        AND expired = '0' ";

This doesn't reduce the amount you write, but it helps substantially in reading and debugging your queries.

The other way is if you're noticing you're testing the same column to for multiple conditions, instead of writing out each [column] = [condition], try using the IN keyword. For example, the following query:

SELECT *
FROM users
WHERE user_id = 5
OR user_id = 6
OR user_id = 10
OR user_id = 99

Can be written like:

SELECT *
FROM users
WHERE user_id IN (5,6,10,99)

It should work with strings (not 100% sure, but don't see why not) but I find it works best with integers. On the flip side, if you're trying to exclude something, you can use NOT IN.

Hope this helps.

johanafm

Bonesnap;10990438 wrote:
It should work with strings

It does work with strings, if you're checking for equality, but not when doing wildcard comparisons with LIKE. I.e.

WHERE txt = 'string1' OR txt = 'string2'
-- is the same as
WHERE txt IN ('string1', 'string2')


WHERE txt LIKE 'string1%' OR txt LIKE 'string2%'
-- is not the same as
WHERE txt IN ('string1%', 'string2%')

scraff

Thanks guys, Ive got it working now, Ive no idea why it wasnt before, but its done it what ever it was! I had been trying to get the IN working but I dont think this is what I really needed, because I needed to use the LIKE '%string%'

Thanks for your help