Conditional PHP and Mysql Query

Well I have this query

$query = sprintf("SELECT price,
                         address, 
                         state, 
                         thumbnail, 
                         name, 
                         provider, 
                         category, 
                         latitude, 
                         longitude,
                         ( 6371 * acos( cos( radians('%s') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( latitude ) ) ) ) AS distance 
                    FROM records 
                   WHERE category IN ("1,2,3") 
                     AND active = '1' 
                  HAVING distance < '%s' 
                ORDER BY distance",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat),
mysql_real_escape_string($radius));

It basically grabs the coordinates from the table Deals and calculates the distance between those points and the user entered coordinates ($center_lat,lng). It then orders it based on distance.

Some records in the database don't have longitudes and latitudes and therefore won't be returned and the records that don't have coordinates have coordinates = '0' where as records that do have coordinates ha开发者_开发技巧ve coordinates = '1'.

My question is what if I want to also return records that don't have coordinates? how do I bypass the distance calculation process if the coordinates = '0'.

---

Add another clause to the 'HAVING' section:

SELECT ...
HAVING ((distance < 1) or (coordinates = 0)) AND $price
...

---

I may be wrong but I do not see any condition in your query that checks if "latitude IS NOT NULL" or "longitude IS NOT NULL" or "coordinates = '1'". This means that your query should return all records that have coordinates or don't have coordinates. For the latter, of course the SELECT output will not have any values in 'latitude', 'longitude' and 'distance' fields.

I think you do not need to bypass the distance calculations for 'coordinates = 0' because that will anyways return NULL, but if you want to you can do it using CASE function, like below:

SELECT price,
address,
state,
thumbnail,
name,
provider,
category,
latitude,
longitude,
(
CASE 
WHEN coordinates = '1'
THEN ( 6371 * 
    acos( 
    cos( radians('%s') ) * 
    cos( radians( latitude ) ) * 
    cos( radians( longitude ) - radians('%s') ) + 
    sin( radians('%s') ) * 
    sin( radians( latitude ) ) 
    ) )
ELSE 0
) AS distance 
FROM records 
...
...