jeudi 24 janvier 2019

If count of rows is 0, change paramter until rows returned - 1 single query?

The primary reason for rows to be returned or not is due to $maxDistance. It's default is 0.2. It more often than not returns results. However, there are cases where it doesn't and 0.3 would of returned results.

Is it possible to somehow use IF and COUNT in MySQL to continue to run the following statement so that if no rows are returned, $maxDistance will try up until 0.5 before giving up?

SELECT property.paon, property.saon, property.street, property.propertyType, property.postcode, property.bedrooms, 
property.receptions, property.lastSalePrice, max(DATE(property.lastTransferDate)), epc.ADDRESS1, epc.TOTAL_FLOOR_AREA, epc.PROPERTY_TYPE 
, ACOS( SIN($lat*PI()/180)
* SIN(property.latitude * PI() / 180 ) 
+ COS($lat*PI()/180)
* COS(property.latitude  * PI() / 180 )
* COS(property.longitude * PI() / 180
- $lng * PI() / 180 )
) * 3957 as distance


FROM property property
INNER JOIN epc ON property.postcode = epc.POSTCODE AND CONCAT(property.paon, ', ', property.street) = epc.ADDRESS1 
WHERE property.lastTransferDate >= NOW() - INTERVAL $maxLastSaleDateYears YEAR
$typeSQL 
$buildSQL 
$minFloorSQL $maxFloorSQL


and property.longitude
BETWEEN $lng - ($maxDistance / abs(cos(radians($lat))*69))
AND $lng + ($maxDistance / abs(cos(radians($lat))*69))

AND property.latitude  
BETWEEN $lat - ($maxDistance / 69)
AND $lat + ($maxDistance / 69)

GROUP BY property.paon, property.postcode
HAVING distance < $maxDistance
ORDER BY distance ASC 
LIMIT 100

Aucun commentaire:

Enregistrer un commentaire