mysql and or query run together

Question

I need to make a search engine where a user can search by name,course,member,year(text field) from the table fsb_profile fields are profile_name,profile_course,profile_member,profile_year

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?? i am making the code like:-

$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

try this query. using this query you can extract details using the combination of search factors

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

Answer 2

If I understand you correctly, you want to search so that either all the fields match or that at least two fields match?

In that case I'd try the following:

$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']."'
         AND 
         (
           profile_member= '".$_REQUEST['type']."' OR
           profile_year= '".$_REQUEST['year']."' OR
           profile_course='".$_REQUEST['course']."'"
         )
      )
      OR
      (
         profile_member= '".$_REQUEST['type']."'
         AND
         (  
           profile_year= '".$_REQUEST['year']."' OR
           profile_course='".$_REQUEST['course']."'"
         )
      )
      OR
      (
         profile_year= '".$_REQUEST['year']."' AND
         profile_course='".$_REQUEST['course']."'"
      )
    )

This returns all sets where either all criteria match or a combination of at least two other criteria matches. I didn't try this really, but that's what I'd start off with.

Answer 3

First off, I would advise you to sanitize your input data. You should NEVER put user-entered data into an SQL query without checking it; that's just asking for trouble.

As for your question, it seems like you're having some trouble with the logic (ANDs and ORs) in your statement. With the statement you are using, you will get all records that match all four fields entered in the search engine, as well as all records that match ANY of the four fields entered. It might be best for you to just construct the query string on the fly, something like:

$arr = sanitize_data($_REQUEST);
$query = "select * from fsb_profile ";
$count = 0;
if ( isset($arr['name']) ) {
    $query .= (($count > 0)?"and":"where")." profile_name = '".$arr['name']."' ";
    count++;
}
if ( isset($arr['type']) ) {
    $query .= (($count > 0)?"and":"where")." profile_member = '".$arr['type']."' ";
    count++;
}
if ( isset($arr['year']) ) {
    $query .= (($count > 0)?"and":"where")." profile_year = '".$arr['year']."' ";
    count++;
}
if ( isset($arr['course']) ) {
    $query .= (($count > 0)?"and":"where")." profile_course = '".$arr['course']."' ";
    count++;
}