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