我有两个变量来定义查询的价格范围。我试图解决的问题是,当这些没有设置,在这种情况下,我想要显示所有行(从1,如果下边界为null,和max(price),如果上边界为null)。
我试过ifnull
,但没有成功。
$priceFrom = $_POST['priceFrom'];
$priceTo = $_POST['priceTo'];
if(is_null($priceFrom) || is_null($priceTo)){
$priceFrom = 0;
$priceTo = 0;
}
$mass = array();
foreach($data as $current){
$sql = "SELECT p.price,
p.type,
p.area,
p.floor,
p.construction,
p.id as propertyID,
CONCAT(u.name, ' ',u.family) as bname,
p.type as ptype,
n.name as neighborhoodName,
CONCAT(o.name,' ',o.surname,' ',o.family) as fullName
FROM `property` p
LEFT JOIN `neighbour` n ON p.neighbour = n.id
RIGHT JOIN `owners` o ON p.owner = o.id
LEFT JOIN users u ON p.broker = u.id
WHERE `neighbour`= '$current'
AND `price` BETWEEN ifnull('$priceFrom', '1') AND ifnull('$priceTo','2000000')
";}
SQL注入
^请Google一下!你的代码非常脆弱!您的数据可能被窃取或删除…
你必须至少用mysqli_real_escape_string()
更好的做法是对SQL注入采取适当的对策,并使用准备好的语句和参数化查询!(如下面的代码所示)
我认为最好的方法是根据变量的值修改查询来处理逻辑:
$sql = "SELECT p.price,
p.type,
p.area,
p.floor,
p.construction,
p.id as propertyID,
CONCAT(u.name, ' ',u.family) as bname,
p.type as ptype,
n.name as neighborhoodName,
CONCAT(o.name,' ',o.surname,' ',o.family) as fullName
FROM `property` p
LEFT JOIN `neighbour` n ON p.neighbour = n.id
RIGHT JOIN `owners` o ON p.owner = o.id
LEFT JOIN users u ON p.broker = u.id
WHERE `neighbour`= :current "; //note: ending white space is recommended
//lower boundary clause -- if variable null - no restriction
if(!is_null($priceFrom){
sql = sql . " AND `price` >= :priceFrom "; // note: whitespace at end and beginning recommended
}
//upper boundary -- better than to set it to an arbitrary "high" value
if(!is_null($priceTo)){
sql = sql . " AND `price` <= :priceTo "; // note: whitespace at end and beginning recommended
}
这种方法允许任何上限值:如果出现严重的通货膨胀,不同的货币,或者突然代码将用于出售房屋,并且将出现价格> 200000的产品,您不需要出去更改大量代码以使其显示…
当然,在执行查询时需要绑定参数:
$stmt = $dbConnection->prepare(sql);
$stmt->bind_param('current', $current);
if(!is_null($priceFrom)){
$stmt->bind_param('priceFrom', $priceFrom);
}
if(!is_null($priceTo)){
$stmt->bind_param('priceTo', $priceTo);
}
//execute and process in same way
$stmt->execute();
另请注意:从你的代码中,似乎你是在循环中发出查询。这是坏习惯。如果循环处理的数据来自
- from DB ->使用
JOIN
元素最好使用
IN
子句。在一次查询中获取所有数据。这在组织和维护代码方面有很大帮助,并且在大多数情况下通常会带来更好的性能。