PHP MySQL中如何获取不符合尺寸筛选条件的产品数据

我有一个products表，存储产品的width（宽度）和height（高度）属性，示例数据如下：

我做了一个按宽度和高度筛选产品的功能，目前代码能根据不同的product_size值返回对应范围的产品，但else分支需要写SQL语句，取出**所有不满足前面任何筛选条件且status='1'**的产品。现有代码如下：

if (isset($_POST['product_size'])) {
  if ($_POST['product_size'] == '60x90') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 50 AND 70 AND product_height BETWEEN 70 AND 110 AND status = '1'";
  } elseif ($_POST['product_size'] == '80x120') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 70 AND 90 AND product_height BETWEEN 110 AND 140 AND status = '1'";
  } elseif ($_POST['product_size'] == '100x150') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 90 AND 110 AND product_height BETWEEN 140 AND 170 AND status = '1'";
  } elseif ($_POST['product_size'] == '120x180') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 110 AND 130 AND product_height BETWEEN 170 AND 200 AND status = '1'";
  } elseif ($_POST['product_size'] == '150x200') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 140 AND 160 AND product_height BETWEEN 200 AND 220 AND status = '1'";
  } elseif ($_POST['product_size'] == '170x240') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 160 AND 180 AND product_height BETWEEN 220 AND 260 AND status = '1'";
  } elseif ($_POST['product_size'] == '200x300') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 180 AND 240 AND product_height BETWEEN 260 AND 330 AND status = '1'";
  } elseif ($_POST['product_size'] == '250x350') {
    $get_range = "SELECT * FROM products WHERE product_width BETWEEN 240 AND 260 AND product_height BETWEEN 330 AND 370 AND status = '1'";
  } elseif ($_POST['product_size'] == 'super') {
    $get_range = "SELECT * FROM products WHERE product_width > 260 AND product_height > 370 AND status = '1'";
  } elseif ($_POST['product_size'] == 'special') {
    $get_range = "SELECT * FROM products WHERE product_width < 260 AND product_height > 370 AND status = '1'";
  } else {
    $get_range = "in this section I want to fetch all the products which does not fulfil the filter criteria";
  } 

直接将前面所有筛选条件用NOT()包裹，配合OR组合，就能得到不符合任何前置条件的产品：

else {
    $get_range = "SELECT * FROM products 
                  WHERE status = '1'
                  AND NOT (
                      (product_width BETWEEN 50 AND 70 AND product_height BETWEEN 70 AND 110)
                      OR (product_width BETWEEN 70 AND 90 AND product_height BETWEEN 110 AND 140)
                      OR (product_width BETWEEN 90 AND 110 AND product_height BETWEEN 140 AND 170)
                      OR (product_width BETWEEN 110 AND 130 AND product_height BETWEEN 170 AND 200)
                      OR (product_width BETWEEN 140 AND 160 AND product_height BETWEEN 200 AND 220)
                      OR (product_width BETWEEN 160 AND 180 AND product_height BETWEEN 220 AND 260)
                      OR (product_width BETWEEN 180 AND 240 AND product_height BETWEEN 260 AND 330)
                      OR (product_width BETWEEN 240 AND 260 AND product_height BETWEEN 330 AND 370)
                      OR (product_width > 260 AND product_height > 370)
                      OR (product_width < 260 AND product_height > 370)
                  )";
}

优化建议（可选） #

如果后续要新增或修改筛选条件，重复写SQL容易出错，可以把所有条件存到数组里动态拼接：

// 先定义所有尺寸对应的筛选条件
$sizeFilters = [
    '60x90' => '(product_width BETWEEN 50 AND 70 AND product_height BETWEEN 70 AND 110)',
    '80x120' => '(product_width BETWEEN 70 AND 90 AND product_height BETWEEN 110 AND 140)',
    '100x150' => '(product_width BETWEEN 90 AND 110 AND product_height BETWEEN 140 AND 170)',
    '120x180' => '(product_width BETWEEN 110 AND 130 AND product_height BETWEEN 170 AND 200)',
    '150x200' => '(product_width BETWEEN 140 AND 160 AND product_height BETWEEN 200 AND 220)',
    '170x240' => '(product_width BETWEEN 160 AND 180 AND product_height BETWEEN 220 AND 260)',
    '200x300' => '(product_width BETWEEN 180 AND 240 AND product_height BETWEEN 260 AND 330)',
    '250x350' => '(product_width BETWEEN 240 AND 260 AND product_height BETWEEN 330 AND 370)',
    'super' => '(product_width > 260 AND product_height > 370)',
    'special' => '(product_width < 260 AND product_height > 370)'
];

if (isset($_POST['product_size']) && array_key_exists($_POST['product_size'], $sizeFilters)) {
    $get_range = "SELECT * FROM products WHERE {$sizeFilters[$_POST['product_size']]} AND status = '1'";
} else {
    // 拼接所有条件，用OR连接后取反
    $allFilters = implode(' OR ', $sizeFilters);
    $get_range = "SELECT * FROM products WHERE status = '1' AND NOT ($allFilters)";
}

这种写法维护性更强，新增尺寸只需要在数组里加一行就行，不用改多处SQL。

内容的提问来源于stack exchange，提问作者Kashmiri Ammar