PHP & # 39;在# 39;查询条件'& # 39;收取# 39; - 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'].'"';

(截断)

提前感谢!

你可以用数组来做这件事，但首先不要用这种方式构建查询，因为你很容易受到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'参数化——用户可以通过单选按钮选择条件是AND还是or:

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

请注意，没有使用'andor'的实际值——如果它是一个OR，那么就使用' OR '。任何其他可能由客户在POST中意外发送的内容，例如"——;删除表学生;

必须在if(){}中包含所有

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

我使用这种技术来制作我的过滤器:

$q = 'SELECT name, category, brand, price, imageurl, purchaselink FROM clothes WHERE 1=1 ';

if(isset($_POST['category'])) $q .= ' AND category ="'.$_POST['category'].'"';
if(isset($_POST['brand'])) $q .= ' AND brand = "'.$_POST['brand'].'"';
// here you can add other filters. :) be happy