1,105,334 Community Members

LIKE, AND incompatibility ?

Member Avatar
Szabi Zsoldos
Posting Whiz in Training
244 posts since Jul 2009
Reputation Points: 0 [?]
Q&As Helped to Solve: 35 [?]
Skill Endorsements: 2 [?]
 
0
 

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
Member Avatar
simplypixie
Practically a Master Poster
673 posts since Oct 2010
Reputation Points: 123 [?]
Q&As Helped to Solve: 122 [?]
Skill Endorsements: 6 [?]
 
2
 

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
Member Avatar
adam_k
Veteran Poster
1,056 posts since Jun 2011
Reputation Points: 239 [?]
Q&As Helped to Solve: 212 [?]
Skill Endorsements: 17 [?]
 
0
 

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.

Member Avatar
VR4Creativity
Newbie Poster
2 posts since Feb 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-1
 

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
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article