处理PHP SQL查询中的可选搜索参数

Question

I'm querying my SQL database in a PHP file from up to three optional search fields (passed through by jQuery). Any one, two or three of these fields can be used at any time to make the query as expansive or as narrow as the user likes. If nothing is in a search field nothing will be returned.

I've written the code so far to handle very basic one search queries and have just begun to add in the multiple parameters - this is where it's starting to get tricky. I can query two fields together without too much bother but adding a third LOCATION parameter is beginning to take up too much code for all of the querying possibilities a user might make.

Here's how my PHP file is set up for two parameters:

if (!empty($_POST['title']) && (!empty($_POST['name']))) 
{
    require '../db/connect.php';
    $sql = "SELECT 
               ....
            FROM 
               ....
            WHERE 
                `table 3`.`TRACKTITLE` = '" . mysql_real_escape_string(trim($_POST['title'])) . "' AND `table 3`.`ARTIST` = '" . mysql_real_escape_string(trim($_POST['name'])) . "'";              
}   


if (!empty($_POST['name']))
 {
    require '../db/connect.php';
    $sql = "SELECT 
              ...
            FROM 
              ...
            WHERE 
              `table 3`.`ARTIST` = '" . mysql_real_escape_string(trim($_POST['name'])) . "'";

}   

if (!empty($_POST['title'])) {
    require '../db/connect.php';
    $sql = "SELECT 
            ...
            FROM 
            ...
            WHERE 
            `table 3`.`TRACKTITLE` = '" . mysql_real_escape_string(trim($_POST['title'])) . "'";
}   

$result = mysql_query($sql);
$data = array();
while ($array = mysql_fetch_assoc($result)) {
$data[] = $array;

Which is the simplest way to build a query with multiple optional parameters in PHP, accounting for any additional parameters that might be added on at a later date? I've read up on isnull values but do they perform a similar function to !emtpy?

Answer 1

Do something along this line:

$whereclauses = array();
$subsets = false;

// for every field
if(!empty($_POST['name']))
  {
  $subsets = true;
  $whereclauses[] = " artist = ". mysql_real_escape_string(trim($_POST['name']));
  }

if($subsets)
  {
  $whereclauses = implode(", ". $whereclauses);
  }
else
  {
  $whereclauses ="";
  }

// now build your query