mysql and or query

Question

I need to make a search query, where a user can search by name or course or year or member, any one of above and also the user cam make a search with all the field or any number of field, e.g.;-as only with name & course.

How it is possible? I don't want to make different query, can it be possible with a single query? now my query is:

$query="SELECT * FROM fsb_profile 
    WHERE profile_name = '".$_REQUEST['name']."' 
    AND profile_member= '".$_REQUEST['type']."' 
    AND profile_year= '".$_REQUEST['year']."' 
    AND profile_course='".$_REQUEST['course']."' 
     OR profile_name = '".$_REQUEST['name']."' 
     OR profile_member= '".$_REQUEST['type']."' 
     OR profile_year= '".$_REQUEST['year']."' 
     OR profile_course='".$_REQUEST['course']."'";

but it's not running, it's only searching by any one of the field, how it is possible?

Answer 1

Just use or then your query will work.

$query="SELECT * FROM fsb_profile 
    WHERE profile_name = '".$_REQUEST['name']."' 
     OR profile_member= '".$_REQUEST['type']."' 
     OR profile_year= '".$_REQUEST['year']."' 
     OR profile_course='".$_REQUEST['course']."'";

Not tested.

Answer 2

If you're mixing AND with OR in a where clause, then it's useful to use brackets to indicate exactly how the combination should be expressed: (A AND B) OR C is not the same as A AND (B OR C). SQL will interpret A AND B OR C (without any brackets) in its own manner, but not necessarily how you might expect (or intend) it to be interpreted.

e.g.

WHERE Likes Soccer AND ( Likes Pizza OR Likes Whiskey )

is looking for a person who likes Soccer and likes either Pizza or Whiskey; so it will pick Soccer-lovers who like Pizza, Soccer-lovers who like Whiskey, Soccer-lovers who like both Pizza and Whiskey

WHERE ( Likes Soccer AND  Likes Pizza ) OR Likes Whiskey

is looking for a person who likes both Soccer and Pizza, or a person who likes Whiskey; so it will pick Soccer-lovers who must also like Pizza, or Whiskey-lovers, or Soccer-lovers who must also like Pizza but may also like Whiskey