CODEWITHSUNDEEP
mysqlleft-joininner-joinlatitude-longitude
SReca
SReca

Reputation: 683

Use inner join with mysql lat long query in php

I have this mysql query to do a search radius using Lat and Long as the center position:

$query = sprintf("SELECT Title, DateTime, ( 6371393 * acos( cos( radians('%s') ) * cos( radians( Lat ) ) * cos( radians( Lon ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( Lat ) ) ) ) AS distance FROM Locations 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));

I enter the Lat, Long, and radius in meters. Then the out put is title and datetime but I also need to get the user name from another table using the id.

The tables are:

Location

| Title | DateTime | UserID |

Users

| UserID | Username |

Is there anyway on how to use this query with Inner Join or Left Join? I'm also open to other ways on approaching this for best optimization.

Upvotes: 1

Views: 309

Answers (2)

asifsid88
asifsid88

Reputation: 4701

select title, datetime, username
from location, users
where location.userid = users.userid

or 

select title, datetime, username
from location
join users
on location.userid = users.userid

Upvotes: 1

Abubakkar
Abubakkar

Reputation: 15664

You can use inner join:

select l.title, l.datetime, u.username
from location l inner join users u on l.userid = u.userid

Upvotes: 0

Related Questions