How to create a dynamic mysql query for all users

advertisements

I need your help with my website search functionality. I'm developing a members area wherein users can search other registered users based on certain criteria, or combination of criteria.

My problem now is how to build a dynamic mysql query to suit the need of each combination of search criteria, where the number of criteria is variable.

Normally, I can write with a pre-determined set of criteria using

   WHERE param1 = '$param1'
   AND param2 = '$param2'
   AND param3 = '$param3'

How do I solve this problem?


If the issue is that you don't know which of the criteria the user will pick, but want to return results for "blank" criteria, you can use the following:

$criteria_1 = $_POST['criteria_1'];
$criteria_2 = $_POST['criteria_2'];
$criteria_3 = $_POST['criteria_3'];

if(!$criteria_1 && !$criteria_2 && !$criteria_1) {

     echo "You must select at least one criteria!";

} else {

   // Run query mentioned below and return results.       

}

THe query would then look like:

SELECT * from mytable
WHERE
(criteria1 = '$criteria_1' OR '$criteria_1' = '') AND
(criteria2 = '$criteria_2' OR '$criteria_2' = '') AND
(criteria3 = '$criteria_3' OR '$criteria_3' = '')

This will treat any blank (non-selected) parameters as blank and ignore them. Be aware that with the above, if no criteria are given, it will return all results.

Another way to write the above is:

SELECT * from mytable
WHERE
criteria1 IN ('$criteria_1', '') AND
criteria2 IN ('$criteria_2', '') AND
criteria3 IN ('$criteria_3', '')

Again, allowing for no entry at all to return all criteria1 results.