我需要为表"products"生成一个sql查询。表格结构如下：
item_no pack_no name model sellingprice discount price_rs quality
101 1001 aa 2001 $500 10% Rs.24750 excellent
102 1002 bb 1996 $400 5% Rs.20900 poor
103 1003 cx 1986 $400 5% Rs.20900 good
104 1004 dx 2010 $500 10% Rs.24750 poor
. . . . . . .
. . . . . . .
. . . . . . .
. . . . . . .
500 5000 bx 1998 $200 10% Rs.9900 very good
等等。
我有以下各种条件：
比如
我需要过滤所有的产品
model is between 1990 to 2010
AND
sellingprice is between 600 to 700
AND
discount is between 0 to 20%
AND
quality is between good and excellent
所有这些都在一个SELECT语句中。有可能吗？或者我需要创建不同的语句，然后连接结果吗？
您可以在单个SELECT查询中完成此操作并动态构建它。
例如，假设您可以在1990年至2010年间使用两个组合框创建"模型"。组合框的默认值为"任意"：
$ands = array();
$betweens = array('model', 'sellingprice', ...);
foreach ($betweens as $basekey)
{
$key1 = $basekey . '_from';
if (!isset($_POST[$key1]))
continue;
if ('' == ($val1 = $_POST[$key1]))
continue;
$key2 = $basekey . '_to';
if (!isset($_POST[$key2]))
continue;
if ('' == ($val2 = $_POST[$key2]))
continue;
// Check that val1 and val2 have sane values
// PDO would be a little different, and slightly more complicated
// On the other hand, if NOT using PDO, SQL injection has to be taken into
// account. Just in case.
if (!is_numeric($val1))
$val1 = "'" . mysql_real_escape_string($val1) . "'";
if (!is_numeric($val2))
$val2 = "'" . mysql_real_escape_string($val2) . "'";
$ands[] = "($basekey BETWEEN $val1 AND $val2)";
}
现在，您有了一系列附加的、可选的条件，这些条件可以与AND连接：
$and_query = implode(' AND ', $ands);
如果不为空，则可以使用得到的条件：
$query = "SELECT ... WHERE ( main conditions )";
if (count($ands))
$query .= " AND ( $and_query ) ";
您还可以添加与$betweens中列出的字段不同的字段"相等"条件。
可以在一个选择查询中完成。。。。这是示例：
select field-1, field-2,.....,field-n
from tablename
where (field-1 between value-1 and value-2)
AND (field-2 between value-3 and value-4)
AND (field-3 IN ('value-5', 'value-6',...,'value-n'))
............
AND (field-n ....[CONDITION].......)
where [CONDITION]
是的，这是可能的。
Select * from table
where
(model between 1990 and 2010)
AND
(sellingprice between 600 and 700)
AND
(discount between 0 and 20)
AND
(quality IN ('good', 'very good', 'excellent'));
SELECT *
FROM products
WHERE model >=1990 AND model <= 2010
AND sellingprice >= 600 AND sellingprice <=700
AND discount <= 20
AND (quality ="good" OR quality ="very good" OR quality ="excellent")
只有当"$"answers"%"符号未存储在DB 中