PHP MySQL 价格范围不起作用

Question

我正在尝试将价格范围与这样的 SELECT 下拉列表一起使用:

<select name="valor">
    <option value="" selected="selected">Faixa de Valor</option>
    <option value="100000.00 , 200000.00">De R$ 100 a 200 mil</option>
    <option value="200000.00 , 500000.00">De R$  200 a 500 mil</option>
    <option value="500000.00 , 1000000.00">De R$  500 a 1 milhão</option>
    <option value="1000000.00 , 5000000.00">De R$ 1 a 5 milhões</option>
    <option value="5000000.00">Acima de 5 Milhões</option>
</select>

我的 PHP 语句是:

$valor_query = $_POST['valor'];
$values = explode(' , ', $valor_query);
$valor1 = $values[0];
$valor2 = $values[1];
$statements .= " AND ( (property_selling.valor >= '$valor1' AND property_selling.valor >= '$valor2') OR (property_renting.valor >= '$valor1' AND property_renting.valor >= '$valor2') ) ";

但它不起作用，我有 2 个不同的表，一个用于销售，另一个用于出租，我将此语句添加为这样的变量:

$filtraSegmento = "SELECT * FROM properties, property_complements,
property_details, property_selling, property_renting 
WHERE property_complements.imovel_id = properties.property_id 
AND property_details.imovel_id = properties.property_id 
AND (property_renting.imovel_id = properties.property_id 
OR property_selling.imovel_id = properties.property_id) 
AND properties.property_status = 'Aprovado' $statements 
GROUP BY properties.property_id";

有什么提示吗？

编辑

按照@Strawberry 和@Sean 的建议，我使用 LEFT JOIN 重新创建了查询，现在它看起来像:

$filtraSegmento = "
SELECT p.*, c.*, d.*, s.*, r.* 
   FROM properties p
   LEFT 
   JOIN property_complements c
     ON c.imovel_id = p.property_id
   LEFT 
   JOIN property_details d 
     ON d.imovel_id = p.property_id
   LEFT 
   JOIN property_selling s 
     ON s.imovel_id = p.property_id
   LEFT 
   JOIN property_renting r 
     ON r.imovel_id = p.property_id
  WHERE $statements
";

它现在运行良好，但我的问题是，我将其用作过滤器，而用户继续过滤时，变量 $statements 不断添加新条件。所以我有这样的数组格式:

$statements = '';
if (isset($_POST['segmento']) && !empty($_POST['segmento'])) {
   $segmento_query = $_POST['segmento'];
    $statements .= " detail.segmento = '$segmento_query' "; //condition for each property
}
if (isset($_POST['cidade']) && !empty($_POST['cidade'])) {
   $cidade_query = $_POST['cidade'];
   $statements .= " detail.cidade = '$cidade_query '";
}
if (isset($_POST['tipo_imovel']) && !empty($_POST['tipo_imovel'])) {
   $tipo_query = $_POST['tipo_imovel'];
   $statements .= " detail.tipo_imovel = '$tipo_query '";
}
if (isset($_POST['local']) && !empty($_POST['local'])) {
   $local_query = $_POST['local'];
   $statements .= " property.property_category = '$local_query '";
}
if (isset($_POST['valor']) && !empty($_POST['valor'])) {
   $valor_query = $_POST['valor'];
       $values = explode(',', $valor_query);
       $valor1 = $values[0];
       $valor2 = $values[1];
       $statements .= " selling.valor BETWEEN '$valor1' AND '$valor2' OR renting.valor BETWEEN '$valor1' AND '$valor2' ";
}

如何将其作为新条件添加到查询中？

Answer 1

您可以像嵌套条件运算符一样尝试而不是分解提交的值。使用像 1,2,3,.. 这样的值作为选项，在你的 php 中可以这样做。

$r = $_POST["valor"]
 ".(
    empty($r) ? "" : ( ($r == "0") ? "" :
        ( ($r == "1") ? "and rent <= 5000" :
             ( ($r == "2") ? "and rent between 5000 and 10000" : "and rent > 10000" )                  
             )
        )
)."