mysql and or query together

Question

I need to make a search engine where a user can search by name,year,member,year(text field)

search will be with any one field or search will be with all the field or search will be with more than one field -How it is possible by using only one query?? now my code 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 is not working?

Answer 1

You could use something like this:

SELECT ...
FROM ...
WHERE (profile_name = '$profile_name' OR '$profile_name' = '')
  AND (profile_member = '$profile_member' OR '$profile_member' = '')
  AND ...

Alternatively you could build the query dynamically based on the fields you have.

Either way you should avoid putting data directly from user input into a query without first escaping it. Take a look at mysql_real_escape_string or use bind parameters.

Answer 2

This should do the trick:

$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']."'");

This checks if all fields are set and if so, it will return that result, otherwise if any combination of the fields are set.