TalkPHP - View Single Post

oceanrockn · 10-07-2010, 07:24 PM

I am trying to join two tables together. First is a calendar of events with lat/lng sorted by distance. I also need to get the business information from the directory table.

This one works without joining the tables without the JOIN:

PHP Code:


			
$query = "SELECT calendar.id,calendar.coupon,( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM calendar HAVING distance < $miles";

This is the code that does not work, have tried many variations:

PHP Code:


			
$query = "SELECT calendar.id,calendar.coupon,directory.business,( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM calendar RIGHT JOIN directory ON calendar.id = directory.id HAVING distance < $miles";

If anyone can help it would be much appreciated

10-07-2010, 07:24 PM	#1 (permalink)
oceanrockn The Visitor Join Date: Oct 2010 Location: Pompano Beach, FL Posts: 1 Thanks: 0	Join Tables with Distance Calculation I am trying to join two tables together. First is a calendar of events with lat/lng sorted by distance. I also need to get the business information from the directory table. This one works without joining the tables without the JOIN: PHP Code: `$query = "SELECT calendar.id,calendar.coupon,( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM calendar HAVING distance < $miles";` This is the code that does not work, have tried many variations: PHP Code: `$query = "SELECT calendar.id,calendar.coupon,directory.business,( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance FROM calendar RIGHT JOIN directory ON calendar.id = directory.id HAVING distance < $miles";` If anyone can help it would be much appreciated