Search results screwed

[benwhitmore]

hi peeps

I have a very simple query that works fine:-

[code]

$query="SELECT id FROM stock WHERE category1 LIKE '%$search%' OR subcategory1 LIKE '%$search%'";
$query_res=mysql_query($query) or die(mysql_error());

[/code]

this works fine, the rest of the code returns values that matches my search criteria. If however, I add one more variable to my SELECT statement, PHP seems to ignore it. I want the SELECT statement to choose items where a field named "omit" has a value of "n":-

[code]

$query="SELECT id FROM stock WHERE omit='n' AND category1 LIKE '%$search%' OR subcategory1 LIKE '%$search%'";
$query_res=mysql_query($query) or die(mysql_error());

[/code]

I have items being returned where the value of "omit" = "y"

Have I used incorrect syntax in my SELECT statement?

any help will flatter my eyelids

Cheers

[GingerRobot]

I would guess this is all down to the use of AND and OR.

I believe that, at present, your statement retrives rows where ethier (omit=n and category1 is like $search ) OR (subcategory1 is like $search)

I hope the use of brackets there shows you what i mean; if omit=yes but subcatgeory1 is like $search, then the result will still be returned. Im not 100% on the solution, i have a feeling you might be able to use brackets in some way with the where statement, but i also think if you added on an extra part to the statement like this:

$query="SELECT id FROM stock WHERE omit='n' AND category1 LIKE '%$search%' OR subcategory1 LIKE '%$search%' AND omit='n'";

It might work. Dont hold me to it though ;)

[benwhitmore]

mate,

it took someone to look outside the box - you hit the nail on the head ole boy!!

OLD CODE:

[code]
"omit='n' AND category1 LIKE '%$search%' OR subcategory1 LIKE '%$search%'";
[/code]

NEW CODE:

[code]
"omit='n' AND category1 LIKE '%$search%' OR omit='n' AND subcategory1 LIKE '%$search%'";
[/code]

thanks again!!!!!

SOLVED