CODEWITHSUNDEEP
phpsqloptional-parameters
Roy
Roy

Reputation: 745

How to create a SQL SELECT query with optional parameters

I've got a PHP method with a number of parameters, all of which are optional. A SQL query uses these parameters to select from a database of employees where each parameter is like a skill/role. It looks something like this:

getMedicalStaff($active = 1, $consultant = 0, $ana = 0, $outreach = 0, $prescribedBy = 0, $respiteCare = 0)

What's the best way to create a SQL query to select rows from a database bearing in mind each parameter may or may not be needed? I'm aware it would be theoretically possible to call this method and ask for nothing, but in practice that's unlikely to happen.

I've tried a few different methods but none work properly. My first attempt was something like this:

// Build query
$argActive = ($active == 1) ? 'true' : 'false';

SELECT * FROM MedStaff WHERE Active = $argActive

But this is obviously flawed because it will demand certain fields be explicitly false when their value doesn't matter.

The next thing I tried was building the SQL query like so:

$argActive = (active == 1) ? 'active = true' : '';

But then I would have to mess about filling in the gaps between each argument variable with AND without knowing if the next argument variable actually contained anything or not.

I've also tried this:

$argActive = ($active == 1) ? '= true' : 'IS NULL';

$sql = "SELECT * FROM MedStaff WHERE Active $argActive

The problem is I can't guarantee the database fields are explicitly NULL even if they are 'empty' (I inherited the database design) so this does not appear to work.

What is the best way to solve my dilemma? It feels like a problem that has been come across many times before and must have a standard accepted answer but I have not been able to find one that worked in my Googling.

Upvotes: 0

Views: 1701

Answers (1)

Barmar
Barmar

Reputation: 780869

Use an array of conditions:

$wheres = array();
if ($active) {
    $wheres[] = 'active = true';
}
if ($consultant)
    $wheres[] = 'consultant = true';
}

Then combine them with implode():

$where_string = implode(' AND ', $wheres);

$sql = "SELECT * FROM MedStaff";
if ($where_string) {
    $sql .= " WHERE " . $where_string;
}

Upvotes: 1

Related Questions