How to query a table based on up to 17 different variables?

I have a MySQL table which I wish to search via a POSTed form. The trouble is there are some 17 variables that an end user may wish to query, either singly or in combination. This results in too many possibilities for else-if statements. After asking here, I've constructed the following query:

$query = "SELECT * FROM profiles"; 

$postParameters = array("name","height","gender","class","death","appro","born","tobiano","modifier","adult","birth","sire","dam","breeder","owner","breed","location");
$whereClause = " WHERE 1 = 1";
foreach ($postParameters as $param) {
    if (isset($_POST[$param]) && !empty($_POST[$param])) {
        switch ($param) {
            case "name":
                $whereClause .= " AND ProfileName='".$_POST[$param]."' ";
                break;
            case "height":
                $whereClause .= " AND ProfileHeight='".$_POST[$param]."' ";
                break;
            case "gender":
                $whereClause .= " AND ProfileGenderID='".$_POST[$param]."' ";
                break;
            case "class":
                $whereClause .= " AND ProfileBreedClassID='".$_POST[$param]."' ";
                break;
            case "death":
                $whereClause .= " AND ProfileYearOfDeath='".$_POST[$param]."' ";
                break;
            case "appro":
                $whereClause .= " AND ProfileYearApproved='".$_POST[$param]."' ";
                break;
            case "born":
                $whereClause .= " AND ProfileYearOfBirth='".$_POST[$param]."' ";
                break;
            case "tobiano":
                $whereClause .= " AND ProfileTobianoTest='".$_POST[$param]."' ";
                break;
            case "modifier":
                $whereClause .= " AND ProfileColourModifier='".$_POST[$param]."' ";
                break;
            case "adult":
                $whereClause .= " AND ProfileAdultColourID='".$_POST[$param]."' ";
                break;
            case "birth":
                $whereClause .= " AND ProfileBirthColourID='".$_POST[$param]."' ";
                break;
            case "sire":
                $whereClause .= " AND ProfileSireReg='".$_POST[$param]."' ";
                break;
            case "dam":
                $whereClause .= " AND ProfileDamReg='".$_POST[$param]."' ";
                break;
            case "breeder":
                $whereClause .= " AND ProfileBreederID='".$_POST[$param]."' ";
                break;
            case "owner":
                $whereClause .= " AND ProfileOwnerID='".$_POST[$param]."' ";
                break;
            case "breed":
                $whereClause .= " AND ProfileBreedID='".$_POST[$param]."' ";
                break;
            case "location":
                $whereClause .= " AND ProfileLocat开发者_Go百科ionCountryID='".$_POST[$param]."' ";
                break;
        }
    }
}
$query .= $whereClause;

$result = mysql_query("$query");
    while ($row = mysql_fetch_array($result)) {
        echo $row['ProfileName'] . '<br/>';
    }

Thanks to Saggi Malachi for this method!

---

A better approach would be defining an array with all possible POST parameters and then running over them while you build your WHERE clause using a PHP switch statement.

<?php
$postParameters = array("name","height","gender");
$whereClause = " WHERE 1 = 1";
foreach ($postParameters as $param) {
    if (isset($_POST[$param]) && !empty($_POST[$param])) {
        switch ($param) {
            case "name":
                $whereClause .= " AND ProfileName='".$_POST[$param]."' ";
                break;
            case "height":
                $whereClause .= " AND ProfileHeight='".$_POST[$param]."' ";
                break;
            // more cases....  
        }
    }
}
$query .= $whereClause;

---

Another way, a bit more flexible.

<?php

// Use $ to indicate variable name, $ will be stripped when getting data from $_POST.
// The $variable will then be replaced with the data from the form.
// This way you setup the entire condition in the array, more flexible.
$set_data = array(
    'ProfileHeight >= $height',
    'ProfileGenderID = $gender',
    'ProfileTobianoTest LIKE %$tobiano%',
    'ProfileOwnerID = $owner',
);

$where = '';
foreach ($set_data as $str) {
    // Get variable name from str.
    if (!preg_match('/\$([a-zA-Z0-9_]+)/', $str, $matches))
        die('Invalid settings.');

    $name = $matches[1];

    if (!isset($_POST[$name]))
        continue;

    $data = trim($_POST[$name]);

    if ($where != '')
        $where .= ' AND ';
    $where .= str_replace('$' . $name, mysql_real_escape_string($data), $str);
}

if ($where != '') {
    $query = 'SELECT * FROM profiles WHERE ' . $where;
    print "$query\n";
}

---

The top one looks like it would work properly.

Have you tested them?

There was a typo in the first though: 'ProdileHeight' should be 'ProfileHeight' I assume

---

If you are willing to make a few modifs:

$query = "SELECT * FROM profiles"; 
$parameters = "";

foreach ($params as $param => $value)
{
    // Important! validate the content of $value to avoid SQL injections

    if (!empty(param))
    {
         $parameters .= "AND $param LIKE '%" . $value . "%' ";
    }
}
if (!empty($parameters))
{
    $parameters = preg_replace("/^AND/", "WHERE", $parameters, 1); 
}

This requires you to:

1. Rename your parameters to the exact names of the columns
2. Gather all the data in an array instead of separate variables By the way, it looks like you are not checking the parameter variables for SQL injection. If these are user submitted, you should.
3. Do all your comparisons using "LIKE". If this is a problem, you can make a couple special cases to take care of the "LIKE" parameters.

Assuming you are willing to do these changes, it's a lot cleaner.

BTW, you ask about PHP, but your code looks like C#. What gives?