Building SQL statements with optional parameters

I'm working with raw SQL in PHP. What's a good way of building statements with optional parameters? For example, I need to filter user profiles with criteria such as gender, age and origin. In what form would it be best to pass parameters like these?

Here's my current approach:

$params = "";

if( isset($_GET["gender"]) ){

    $gender = "";

    switch( $_GET["gender"] ){
        case "male":
            $gender = " WHERE users.gender = 1 ";
 开发者_开发技巧           break;
        case "female":
            $gender = " WHERE users.gender = 0 ";
            break;
    } 

    $params = $params + $gender;
}

A simple approach that might work for you:

1 - Begin your WHERE clause with "1=1" so that all of your conditions can simply by "AND FIELD = :PARAM", rather than worrying about whether the word "WHERE" has already been added

2 - Use ISNULL() to build a query that basically ignores parameters that are not provided.

Example:

SELECT *
FROM MY_TABLE t
WHERE 1=1
  AND (t.gender = ISNULL(:gender,t.gender))
  AND (t.age >= ISNULL(:age,t.age))

If you are determined to use raw SQL you could roll your own very simple query builder.

There are a number of libraries that do this for you but the basic principle would be to create an object that knows about your 'base query' then set a series of parameters on that object .Then write a method for building your SQL statement according to those parameters.

This might be as simple as setting an array of clauses and then imploding them with an 'AND' statement between them...

You could have something like this as a where clause:

where (:gender is null or gender = :gender) ...

And you pass null for when the user doesn't provide you the parameter.

In your given example, I would probably do something like:

$params = $params . " WHERE `users`.`gender` = " 
  . ($_GET["gender"] == "female" ? "0" : "1");

In the case of truly optional parameters, I usually do something like this:

if (isset($_GET["gender"]))
{
  $params = $params . " WHERE `users`.`gender` = " 
    . ($_GET["gender"] == "female" ? "0" : "1");
}