从表中选择缺少某些WHERE条件的表

Question

I have following sql request

        if(isset($_GET['room'])) {

            if($_GET['status'] == 'all' && $_GET['room'] == 'all'){
                $stmt = $pdo->prepare("SELECT DISTINCT flatnetto FROM flat ORDER BY flatnetto");
                $stmt->execute(array($_GET['room'], $_GET['status']));
            }else if($_GET['status'] == 'all'){
                $stmt = $pdo->prepare("SELECT DISTINCT flatnetto FROM flat WHERE kk = ? ORDER BY flatnetto");
                $stmt->execute(array($_GET['room']));
            }else if($_GET['room'] == 'all'){
                $stmt = $pdo->prepare("SELECT DISTINCT flatnetto FROM flat WHERE sold = ? ORDER BY flatnetto");
                $stmt->execute(array($_GET['status']));
            }else{
                $stmt = $pdo->prepare("SELECT DISTINCT flatnetto FROM flat WHERE kk = ? AND sold = ? ORDER BY flatnetto");
                $stmt->execute(array($_GET['room'], $_GET['status']));
            }               

            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        }

It is possible to make just one sql request from code above? I mean, it is possible to make just one request

    $stmt = $pdo->prepare("SELECT DISTINCT flatnetto FROM flat WHERE kk = ? AND sold = ? ORDER BY flatnetto");

but condition "kk" and "sold" may or may not exist (will have unsortable "all" value). So for example if "kk" not exist just ignore it and continue selecting only with condition "sold".

Code above is working but it is pretty annoying to define all possible options.

---

Status can have values (Sold, Reserved, All)

Room can have values (1kk, 2kk, 3kk, All)

Value "all" means that for example I am interesting on 2kk room and no matter if is "sold" or "reserved" just show me all of them.

UPDATED Question.

As is wrote on answer below it is possible to make one sql request. But the problem persist because we just move "possible option definition" to sql query but I still have to define all possible option. I can not imagine how to define all possible options if we will have not just two input but for example "room, status ,price1, price2, floor, size ..." and all of them can have own value but also value "all". In this case we have to define all possible options from all possible variations (in this case more than 20 possible variations). I think this is not way how it work in bigger projects.

Answer 1

You can use the following code to generate your SQL query

$param=Array();

## associate query parameters to sql column
$keys=Array("room" => "kk", "status" => "sold");

## generate parameter list
foreach ($keys as $k=>$v)
if (isset($_GET[$k])&&($_GET[$k]!='all'))
   $param[$v]=$_GET[$k];

## generate query
$query="SELECT DISTINCT flatnetto FROM flat WHERE ".
    implode(" = ? AND ", array_keys($param)).
    (count($param) ? " = ?" : "1")
    ." ORDER BY flatnetto";

## execute
$stmt = $pdo->prepare($query);
$stmt->execute($param);