Tutorial :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?


Solution: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.


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »