Handling optional search parameters in a PHP SQL query

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?

This is not an answer, using require '../db/connect.php'; in every conditions, you can add require line in top of this lines — Krish R
– Krish R, Commented Dec 28, 2013 at 12:00

Jelle Ferwerda · Accepted Answer · 2013-12-28 12:04:42Z

2

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

answered Dec 28, 2013 at 12:04

Jelle Ferwerda

1,2291 gold badge7 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Byate Over a year ago

Thanks for this - it seems to be pointing me in the right direction! I have a slight issue with the $whereclauses = implode(", ". $whereclauses); - when debugging I get an Array to string conversion error. Any ideas why it's producing this?

Jelle Ferwerda Over a year ago

Hm.. It may not like using the array var. Change to: $whereclause = implode(", ". $whereclauses);

Collectives™ on Stack Overflow

Handling optional search parameters in a PHP SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related