PHP SQL:如果变量为空，则跳过查询部分的方法 [英] PHP SQL: Way to skip over section of a query if variable is blank

问题描述

我正在编写一个查询，该查询使用来自搜索表单的输入，其中品牌"，类型"和价格"是可选输入字段:

I'm writing a query that uses input from a search form where Brand, Type and Price are optional input fields:

SELECT * FROM `database` WHERE `brand` LIKE "%' . $brand . '%" AND `type` LIKE "%' . $type. '%" AND `price` LIKE "%' . $price . '%"

我想知道如果没有任何一个字段输入任何内容，是否有办法说全部".例如，如果他们未在price字段中输入值，则有一种方法告诉SQL只说忽略该部分，例如:

I am wondering if there is a way to say 'all' if nothing is entered into one of the fields. For example if they do not enter a value in the price field is there a way to tell SQL to just say ignore that section, eg:

AND `price` LIKE "*";

因此重新使用的商品仍按品牌和类型过滤，但可以有任何价格.

So the reuslts are still filtered by Brand and Type but can have any Price.

对此有任何建议，不胜感激！谢谢

Any advice on this is appreciated! Thanks

推荐答案

正如Ariel所说，最好在构建查询时让PHP进行过滤.这是这样做的代码示例:

As Ariel mentioned, it would be better to have PHP do the filtering as you build the query. Here's a code sample for doing it that way:

<?php
$sql = 'SELECT * FROM `database`';
$where = array();
if ($brand !== '') $where[] = '`brand` LIKE "%'.$brand.'%"';
if ($type !== '')  $where[] = '`type` LIKE "%'.$type.'%"';
if ($price !== '') $where[] = '`price` LIKE "%'.$price.'%"';
if (count($where) > 0) {
  $sql .= ' WHERE '.implode(' AND ', $where);
} else {
  // Error out; must specify at least one!
}
// Run $sql

注意:请请请确保先清除$brand，$type和$price变量内容，然后再使用它们或使自己容易受到SQL注入攻击的影响(理想情况下，您应该使用PHP PDO 具有准备好的语句的数据库连接器以清理输入).

NOTE: Please, please, please make sure that the $brand, $type, and $price variable contents are sanitized before you use them this way or you make yourself vulnerable to SQL injection attacks (ideally you should be using the PHP PDO database connector with prepared statements to sanitize the input).

这篇关于PHP SQL:如果变量为空，则跳过查询部分的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！