Adjusting sql statement in a function based upon input

Question

Hey there, This is a bit urgent!

I'm trying to make a simple filter search where-by you can choose from a series of 3 drop downs and then based upon this the results are then displayed, How would I go about adjusting the sql query for each and if you were to only choose to search from aone of the 3 rather than all 3 etc... example there could be the url with input such as: url.com?location=gb&color=3&hair=4 and still form the correct sql query for something like this: url.com?location=gb&hair=1 and not encounter problems with WHERE and AND etc etc and empty variables in the statement

Would this not need to be a massive function to check using if to see how the data is set for all possibilities?

Thanks, Stefan

Answer 1

I answered a question the other day that I think is pretty similar to yours:

PHP: prepared statement, IF statement help needed

The idea is that you use conditional logic in your code to collect terms as needed corresponding to your application inputs. Then you join them together in such a way that produces the right SQL expression.

It does need some application function to build the SQL expression dynamically, and there are techniques to make it as concise as possible. If you really have many possible search terms, you might end up with a lengthy function. But guess what? If you have complex inputs, it should be no surprise that you need complex code to deal with them.

---

Re your comment:

url.com?location=gb&color=3&hair=4

Okay, you have up to three inputs and you have to dynamically build up an SQL query from these. Let's start from the end and work backwards. Ultimately you want an SQL expression like this:

WHERE (location = 'gb') AND (color = 3) AND (hair = 4)

If you have an array of three terms, you can join them together in PHP using the implode() function. But you may also have fewer than three. You can handle any number of terms by putting however many terms you have into an array and imploding them with AND between each term:

$where_array = array(
        "(location = 'gb')",
        "(color = 3)",
        "(hair = 4)"
    );

$where_expr = "WHERE " . implode(" AND ", $where_array);

So how do you create the array with these terms? By writing code to append to the array conditionally for each input that is present in your app's current request:

$where_array = array();
if (array_key_exists("location", $_GET)) {
    $location = mysql_real_escape_string($_GET["location"]);
    $where_array[] = "(location = '$location')";
}
if (array_key_exists("color", $_GET)) {
    $color = mysql_real_escape_string($_GET["color"]);
    $where_array[] = "(color = '$color')";
}
if (array_key_exists("hair" $_GET)) {
    $hair = mysql_real_escape_string($_GET["hair"]);
    $where_array[] = "(hair = '$hair')";
}

After all that's done, your array has between zero and three elements. If it has one or more, you want to generate a WHERE clause as shown previously, otherwise skip it.

$where_expr = '';
if ($where_array) {
    $where_expr = "WHERE " . implode(" AND ", $where_array);
}

Then append the $where_expr to your baseline SQL query.

$sql .= $where_expr

The stuff about $params is for query parameters, which is an alternative method of including dynamic values into an SQL expression, instead of mysql_real_escape_string(). It's not mandatory (and in fact PHP's old mysql extension doesn't support query parameters) but I recommend switching to PDO so you can use this feature. See example here: PDO::prepare().

Answer 2

Here is my go at it:

// discard empty values and unwanted keys
$get = array_intersect_key(array_filter($_GET, 'strlen'), array_flip(array('location', 'color', 'hair')));

foreach ($get as $key => $value)
{
    $get[$key] = $key . ' = ' .  mysql_real_escape_string($value);
}

$sql .= ((count($get) == 0) ? null : ' WHERE ') . implode(' AND ', $get);

I haven't tested it but it should work well.