Configuring sql statement in input-based function

Question

This is a little urgent!

I’m trying to do a simple search on filters, where you can choose from three drop-down lists, and then based on this, display the results, as if I wanted to configure an SQL query for each, and if you had to select only one of three, not all three, etc ... For example, there may be a URL with input, for example: url.com?location=gb&color=3&hair=4and still generate the correct SQL query for something like this: url.com?location=gb&hair=1and not encounter problems with WHERE, etc. etc. and empty variables in the statement

Isn't that supposed to be a massive function to test usage if you look at how the data is set for all the features?

Thanks Stefan

Answer 1

The other day I answered a question that, in my opinion, is very similar to yours:

PHP: prepared statement, help from IF statement needed

The idea is that you use conditional logic in your code to collect terms as appropriate for your applications. Then you combine them together to create the correct SQL expression.

Building an SQL expression dynamically requires some application function, and there are methods to make it as concise as possible. If you really have a lot of possible search terms, you can get a long function. But guess what? If you have complex data entry, it is not surprising that you need complex code to work with them.

---

Your comment:

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

, , SQL- . . SQL :

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

, PHP implode(). . , - , AND :

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

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

, ? , :

$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')";
}

. , WHERE, , .

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

$where_expr SQL-.

$sql .= $where_expr

$params , SQL mysql_real_escape_string(). ( PHP mysql ), PDO, . : PDO::prepare().

Answer 2

:

// 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);

, .