this should be easy - simple query problem 1

[jasc2k]

hi all,

I dont use SQL that much as I have no real need but the little I do use it and I come across the following:

SELECT username,email FROM users WHERE username NOT IN('admin','james') AND name LIKE '%test%' OR lastname LIKE '%test%

this may be obvious, I even know the problem however I would simply like the query to match either name OR lastname BUT NOT if username is admin or james

in my statement the NOT IN is ignored when lastname produces a match.
I beleive I have tried it the other way around with even more detrimental effects.

Any help appreciated, cheers

 

[vacunita]

2 things that should be causing issues here:

1. 'name' is a reserved keyword in MYSQL. If your column or field is called "name" it must be surrounded in backticks. [COLOR=yellow red]`[/color].

2. MYSQL runs the where clauses in order, so basically it wants test first for both the NOT IN and 'name' to be true, or just that lastname alone is true. You want to group them the way you want them considered. So surround your LIKEs with parenthesis.

SELECT username,email FROM users WHERE username NOT IN('admin','james') AND [COLOR=white blue]([/color] [COLOR=yellow red]`[/color]name[COLOR=yellow red]`[/color] LIKE '%test%' OR lastname LIKE '%test%' [COLOR=white blue])[/color]

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Behind the Web, Tips and Tricks for Web Development.

http://behindtheweb.blogspot.com/

 

[jasc2k]

hey,

step 2 fixed the issue immediatly thank you so much, I have added the name ticks just in case.

thanks again