php - 在sql中选择查询

Question

我需要为表“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 语句中。是否有可能或者我是否需要创建不同的语句然后连接结果？

Answer 1

您可以在单个 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 中列出的字段不同的字段。