PHP'Where'查询条件为'If''isset'(PHP 'Where' query conditional on

PHP'Where'查询条件为'If''isset'(PHP 'Where' query conditional on 'If' 'isset')

我是PHP的新手，所以对以下任何帮助都会非常感激：

我正在尝试使用WHERE条件运行查询，但我只想在已设置变量的情况下应用where条件（通过单选按钮）。

我基本上希望能够说 - 如果未设置变量，则不要将其包含在where条件中。 复杂的是，我将用3个以上的变量做这个...

最基本的 - 这是我到目前为止的代码：

if(isset($_POST['category'])){ $category = $_POST['category'];} if(isset($_POST['brand'])) { $brand = $_POST['brand']; } { $q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE category = "'.$_POST['category'].'" AND brand = "'.$_POST['brand'].'"';

[截断]

首先十分感谢！

I am new to PHP so any help on the following would be really appreciated:

I am trying to run a query with a WHERE condition, but I only want to apply the where condition if the variable has been set (via a radio button).

I basically want to be able to say - if a variable is not set, do not include it in the where condition. The complication is that I will be doing this with 3+ variables...

At its most basic - here is the code I have so far:

if(isset($_POST['category'])){ $category = $_POST['category'];} if(isset($_POST['brand'])) { $brand = $_POST['brand']; } { $q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE category = "'.$_POST['category'].'" AND brand = "'.$_POST['brand'].'"';

[truncated]

Thanks very much in advance!

最满意答案

您可以使用数组执行此操作，但首先不要以这种方式构建查询，因为您将容易受到SQL注入攻击。 请改用PDO 。

想法是列出合适的条件：

$conds = [ 'brand', 'category', 'name' ]; $where = [ ]; // Empty array foreach ($conds as $cond) { if (!empty($_POST[$cond])) { $sql = "({$cond} = ?)"; // We use PDO and bound values. $where[$sql] = $_POST[$cond]; // In *deprecated* MySQL we would use at least // $sql = "({$cond} = '" . mysql_real_escape_string($_POST[$cond]) . "')"; // $where[$sql] = true; } } // Now we have a list of pairs: // brand = ? => 'MyBrand', // name = ? => 'MyName', if (!empty($where)) { $sql_string .= ' WHERE ('; $sql_string .= implode( ' AND ', array_keys($where) ); $sql_string .= ')'; } // $sql_string is now SELECT ... WHERE ( (brand=?) AND (name=?) ... ) // Using the MySQL version, we would have ... WHERE ( (brand='MyBrand') AND ... ) ) // With PDO we PREPARE the query using sql_string // http://dev.mysql.com/doc/apis-php/en/apis-php-pdo-mysql.html // http://www.php.net/manual/en/intro.pdo.php // We need an open PDO connection saved into $pdo $stmt = $pdo->prepare ($sql_string); // Then we execute the query. // Bind the values to array_values($where). $stmt->execute( array_values($where) ); while ($tuple = $stmt->fetch(PDO::FETCH_ASSOC)) { ... }

更短的方式，只有MySQL（因为它不区分键和值）将是

$where = [ ]; // empty array() foreach ($conds as $cond) { if (empty($_POST[$cond])) { continue; } // THIS IS NOT SECURE. See e.g. http://johnroach.info/2011/02/17/why-mysql_real_escape_string-isnt-enough-to-stop-sql-injection-attacks/ $escaped = mysql_real_escape_string($_POST[$cond]); $where[] = "({$cond} = '{$escaped}')"; } $query = "SELECT ..."; if (!empty($where)) { $query .= " WHERE (" . implode(' AND ', $where) . ")"; }

这种方法还有一个额外的好处，就是您可以对参数进行'AND' - 用户可以通过radiobutton选择是否具有ANDed或ORed条件：

$and_or = ('OR' == $_POST['andor']) ? ' OR ' : ' AND '; $query .= " WHERE (" . implode($and_or, $where) . ")";

请注意，不使用'andor'的实际值 - 如果它是OR，则表示良好且良好，使用'OR'。 客户在POST中意外发送的任何其他内容，例如“ - ; DROP TABLE Students; ”， 均被视为“AND”。

You can do this with an array, but first of all DO NOT BUILD QUERIES THIS WAY since you'll be vulnerable to SQL injection. Use PDO instead.

The idea is to have a list of suitable conditions:

$conds = [ 'brand', 'category', 'name' ]; $where = [ ]; // Empty array foreach ($conds as $cond) { if (!empty($_POST[$cond])) { $sql = "({$cond} = ?)"; // We use PDO and bound values. $where[$sql] = $_POST[$cond]; // In *deprecated* MySQL we would use at least // $sql = "({$cond} = '" . mysql_real_escape_string($_POST[$cond]) . "')"; // $where[$sql] = true; } } // Now we have a list of pairs: // brand = ? => 'MyBrand', // name = ? => 'MyName', if (!empty($where)) { $sql_string .= ' WHERE ('; $sql_string .= implode( ' AND ', array_keys($where) ); $sql_string .= ')'; } // $sql_string is now SELECT ... WHERE ( (brand=?) AND (name=?) ... ) // Using the MySQL version, we would have ... WHERE ( (brand='MyBrand') AND ... ) ) // With PDO we PREPARE the query using sql_string // http://dev.mysql.com/doc/apis-php/en/apis-php-pdo-mysql.html // http://www.php.net/manual/en/intro.pdo.php // We need an open PDO connection saved into $pdo $stmt = $pdo->prepare ($sql_string); // Then we execute the query. // Bind the values to array_values($where). $stmt->execute( array_values($where) ); while ($tuple = $stmt->fetch(PDO::FETCH_ASSOC)) { ... }

A shorter way, MySQL only (since it does not distinguish between keys and values) would be

$where = [ ]; // empty array() foreach ($conds as $cond) { if (empty($_POST[$cond])) { continue; } // THIS IS NOT SECURE. See e.g. http://johnroach.info/2011/02/17/why-mysql_real_escape_string-isnt-enough-to-stop-sql-injection-attacks/ $escaped = mysql_real_escape_string($_POST[$cond]); $where[] = "({$cond} = '{$escaped}')"; } $query = "SELECT ..."; if (!empty($where)) { $query .= " WHERE (" . implode(' AND ', $where) . ")"; }

This approach has the additional advantage that you can have the 'AND' parameterized - the user can choose whether have the conditions ANDed or ORed via a radiobutton:

$and_or = ('OR' == $_POST['andor']) ? ' OR ' : ' AND '; $query .= " WHERE (" . implode($and_or, $where) . ")";

Note that the actual value of 'andor' is NOT used -- if it is an OR, all well and good, ' OR ' is used. Anything else that might be accidentally sent in a POST by a customer, such as "--; DROP TABLE Students;" , is considered to mean ' AND '.