Jump to content


Photo

Search results screwed


  • Please log in to reply
2 replies to this topic

#1 benwhitmore

benwhitmore
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 28 May 2006 - 02:48 PM

hi peeps

I have a very simple query that works fine:-


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


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":-


$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());


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

#2 GingerRobot

GingerRobot
  • Staff Alumni
  • Advanced Member
  • 4,086 posts
  • LocationUK

Posted 28 May 2006 - 04:10 PM

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 ;)

#3 benwhitmore

benwhitmore
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 28 May 2006 - 05:01 PM

mate,

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

OLD CODE:

"omit='n' AND category1 LIKE '%$search%' OR subcategory1 LIKE '%$search%'";

NEW CODE:

"omit='n' AND category1 LIKE '%$search%' OR omit='n' AND subcategory1 LIKE '%$search%'";

thanks again!!!!!

SOLVED




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users