LIKE, AND incompatibility ?

[Szabi Zsoldos]

Hi guys,

I have a database with hundreds of persons and doing a live-search with ajax.

And I have this query.
The idea is that I do have the test line in the db but it has a status 0, but it is keep showing up in the results even if I do a search based on the status = 1.

What could be my problem in this simple query ?

I've search the forums but status is not an explicit word but only if I use it as a SHOW STATUS

SELECT * FROM contact WHERE  status = '1'
                                  AND lower(concat(nume,' ',prenume)) LIKE '%test%' 
                                        AND lower(concat(prenume,' ',nume)) LIKE '%test%'                                   
                                        OR functie LIKE '%test%' 
                                        OR email LIKE '%test%' 
                                        OR compartiment LIKE '%test%' 
                                        OR email LIKE '%test%' 
                                        OR user_sap LIKE '%test%'
                                        OR atrib LIKE '%test%'
                                        OR user_mtr LIKE '%test%' 
                                        OR interior LIKE '%test%' 
                                        OR mobil_firma LIKE '%test%' 
                                      ORDER BY nume, prenume LIMIT 7

[simplypixie]

If you are mixing AND and OR in your queries you need to group them into parenthesis

SELECT * FROM contact WHERE  status = '1'
AND lower(concat(nume,' ',prenume)) LIKE '%test%' 
AND (lower(concat(prenume,' ',nume)) LIKE '%test%'                                   
OR functie LIKE '%test%' 
OR email LIKE '%test%' 
OR compartiment LIKE '%test%' 
OR email LIKE '%test%' 
OR user_sap LIKE '%test%'
OR atrib LIKE '%test%'
OR user_mtr LIKE '%test%' 
OR interior LIKE '%test%' 
OR mobil_firma LIKE '%test%') 
ORDER BY nume, prenume LIMIT 7

[adam_k]

OR will "reset" your where clause and start over if you don't use parenthesis - just like simplypixie said.
Basically it's like

where somecondition here 
OR -- whatever happened before the OR doesn't matter 
some other confition 
OR -- same here, it doesn't matter what the first condition returned or what the second condition returned. 

You can either type it like:

where status = '1' and lower(concat(nume, ' ' ,prenume)) LIKE '%test%' 
and lower(concat(prenume, ' ' , nume)) LIKE '%test%'
or status = '1' and functie LIKE '%test%' 

So basically repeat the whole set of criteria for every OR, or do it like simplypixie's example (which is how everybody does it). Basically you'll allow OR to "reset" only the conditions inside the parenthesis that the OR appears.

[VR4Creativity]

Try this one....

SELECT * FROM contact WHERE

SELECT * 
FROM contact WHERE  
    STATUS = '1' AND 
    (
        LOWER(CONCAT(nume,' ',prenume)) LIKE '%test%' OR 
        LOWER(CONCAT(prenume,' ',nume)) LIKE '%test%' OR 
        functie LIKE '%test%'  OR 
        email LIKE '%test%' OR 
        compartiment LIKE '%test%' OR 
        email LIKE '%test%' OR 
        user_sap LIKE '%test%' OR 
        atrib LIKE '%test%' OR 
        user_mtr LIKE '%test%' OR 
        interior LIKE '%test%' OR 
        mobil_firma LIKE '%test%'
    )
ORDER BY nume, prenume LIMIT 7