Hi there everyone!~
I've got this query:
$query_rad = "SELECT user_id, lat, lng,
acos(sin($lat)*sin(radians(lat)) + cos($lat)*cos(radians(lat))*cos(radians(lng)-$lng))*$R AS D
FROM (
SELECT user_id, lat, lng
FROM ".$ss_mm_prefix."mapdata
WHERE lat > $minLat AND lat < $maxLat
AND lng > $minLng AND lng < $maxLng
AND user_id != $viewing_user_id
) As FirstCut
WHERE acos(sin($lat)*sin(radians(lat)) + cos($lat)*cos(radians(lat))*cos(radians(lng)-$lng))*$R < $rad
ORDER BY D";
echo("<table width='100%'>");
$result_rad=mysql_query($query_rad) or die('MySQL error: Failed to retrieve surrounding users <br />' . mysql_error() . '<hr />Query: ' . $query_rad);
$num_rad=mysql_num_rows($result_rad);
if($num_rad != '0'){
while ($row_rad = mysql_fetch_assoc($result_rad)) {
if($uom == 'k'){
$D = round($row_rad['D']*1.60934);
}else{
$D = round($row_rad['D']);
}
$user_id = $row_rad['user_id'];
$user_lat = $row_rad['lat'];
$user_lng = $row_rad['lng'];
/* username for the user_id. */
$query_nearuser="SELECT username FROM ".$phpbb_prefix."users where user_id = '$user_id' LIMIT 1";
$result_nearuser=mysql_query($query_nearuser) or die('MySQL error: Failed to retrieve username for user_id <br />' . mysql_error() . '<hr />Query: ' . $query_nearuser);
$num_nearuser=mysql_num_rows($result_nearuser);
if($num_nearuser != '0'){
while ($row_nearuser = mysql_fetch_assoc($result_nearuser)) {
$username = $row_nearuser['username'];
}
}
And I'm trying to combine the query that grabs the username with the initial query that grabs the nearby users, but I'm not having any luck. Is this something that can be done? Here's my latest effort:
$query_rad = "SELECT user_id, lat, lng,
acos(sin($lat)*sin(radians(lat)) + cos($lat)*cos(radians(lat))*cos(radians(lng)-$lng))*$R AS D
FROM (
SELECT user_id, lat, lng
FROM ".$ss_mm_prefix."mapdata
WHERE lat > $minLat AND lat < $maxLat
AND lng > $minLng AND lng < $maxLng
AND user_id != $viewing_user_id
) As FirstCut a INNER JOIN ".$phpbb_prefix."users b USING(user_id)
WHERE acos(sin($lat)*sin(radians(lat)) + cos($lat)*cos(radians(lat))*cos(radians(lng)-$lng))*$R < $rad
ORDER BY D";
echo("<table width='100%'>");
$result_rad=mysql_query($query_rad) or die('MySQL error: Failed to retrieve surrounding users <br />' . mysql_error() . '<hr />Query: ' . $query_rad);
$num_rad=mysql_num_rows($result_rad);
if($num_rad != '0'){
while ($row_rad = mysql_fetch_assoc($result_rad)) {
if($uom == 'k'){
$D = round($row_rad['D']*1.60934);
}else{
$D = round($row_rad['D']);
}
$user_id = $row_rad['user_id'];
$user_lat = $row_rad['lat'];
$user_lng = $row_rad['lng'];
$username = $row_rad['username'];
Which results in the following error:
MySQL error: Failed to retrieve surrounding users
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'a INNER JOIN phpbb_users b USING(user_id) WHERE acos(sin(0.612393860389)*sin(r' at line 9
Query: SELECT user_id, lat, lng, acos(sin(0.612393860389)sin(radians(lat)) + cos(0.612393860389)cos(radians(lat))cos(radians(lng)--1.46668100538))3951 AS D FROM ( SELECT user_id, lat, lng FROM ss_mm_mapdata WHERE lat > 33.6374246652 AND lat < 36.5377425348 AND lng > -85.8068476435 AND lng < -82.2624153565 AND user_id != 2 ) As FirstCut a INNER JOIN phpbb_users b USING(user_id) WHERE acos(sin(0.612393860389)sin(radians(lat)) + cos(0.612393860389)cos(radians(lat))cos(radians(lng)--1.46668100538))3951 < 100 ORDER BY D
Thanks for your time!
