CODEWITHSUNDEEP
phpformssearch
iamjonesy
iamjonesy

Reputation: 25122

Building an SQL query using multiple (optional) search fields

I have a form that is going to be used to search through a table of support tickets.

the user can search from a few difficult optional fields.

I'm wondering what is the best way to deal with optional search filters. So I have a query that takes in parameters from the user. So if the user searches using both the from and to dates then the query would want to include BETWEEN. So do I have to write a different query for if the user searches for only from. or another query when the user has not added any date parameters? Then what if the status dropdown is blank? Is that another query?

Any help to clear this up would be great!

Jonesy

Upvotes: 0

Views: 4060

Answers (4)

Omidoo
Omidoo

Reputation: 513

This is an elegant way that I use alot and wish will help you too:

$q = 'SELECT * FROM Users';
$buildQ = array();

if (empty($idOrName) === false) {
    $buildQ[] = '(userid = "' . $idOrName . '" OR username LIKE "%' . $idOrName. '%")';
}

if (empty($nickname) === false) {
    $buildQ[] = 'nickname="' . $nickname . '"';
}

if (empty($salary) === false) {
    $buildQ[] = 'salary="' . $salary . '"';
}

// ... any other criterias like above if statements

if (count($buildQ) === 1) {
    $q .= ' WHERE ' . $buildQ[0];
} else if (count($buildQ) > 1) {
    $count = 0;
    foreach ($buildQ as $query) {
        if ($count === 0) {
            $q .= ' WHERE ' . $query;
        } else {
            $q .= ' AND ' . $query;
        }

        $count++;
    }
}

Upvotes: 1

Alex Howansky
Alex Howansky

Reputation: 53543

Hard to say without knowing what sort of DB abstraction you're using, but assuming you're hand-writing the SQL, it's fairly simple, just build up sections of your where clause individually for each variable. (Assuming here that your vars are already escaped/quoted.)

$where_clause = array();
if (!empty($date_from)) {
    $where_clause[] = "table.date >= $date_from";
}
if (!empty($date_to)) {
    $where_clause[] = "table.date <= $date_to";
}
if (!empty($status)) {
    $where_clause[] = "status = $status";
}

$query = 'select * from table where ' . join(' and ', $where_clause);

Upvotes: 3

Daniel Vandersluis
Daniel Vandersluis

Reputation: 94143

Build your query in parts. Start with whatever is constant in your query, and add on more SQL depending on what extra conditions:

$query = "SELECT ...
  FROM ...
  WHERE [where conditions that are always going to be present]";

if (isset($_POST['date_from']) && isset($_POST['date_to']))
{
  $query .= ... // query code for dealing with dates
}

if (isset($_POST['status']))
{
  $query .= ... // deal with status
}

// etc.

// Once you have your query fully built, execute it
$result_set = mysql_query($query);

This code is obviously just a skeleton, but that's how I would construct my query.

Upvotes: 3

jjczopek
jjczopek

Reputation: 3379

I think it would be better if You generate query dynamically at runtime based on which fields are filled. So You could make some helper which appends specific query fragments if only one date is passed and the other one is null, or when both are passed and so on.

Upvotes: 0

Related Questions