Conditional PHP and Mysql Query

Question

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 have 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'.

Marc B · Accepted Answer · 2011-06-19 05:17:57Z

2

Add another clause to the 'HAVING' section:

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

answered Jun 19, 2011 at 5:17

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhay · Accepted Answer · 2011-06-19 07:18:37Z

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 
...
...

Collectives™ on Stack Overflow

Conditional PHP and Mysql Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related