damianb123
asked on
Finding all agents in database from longitude and latitude
Hi,
I have a registration form where users register as either staff or employer, when they register, their longitude and latitude values get automatically populated in my database. What I need to now do is:
When someone logs in they automatically see all other staff within a 10 mile radius of their longitude and latitude.
How can I achieve this?
Many thanks
Damian
I have a registration form where users register as either staff or employer, when they register, their longitude and latitude values get automatically populated in my database. What I need to now do is:
When someone logs in they automatically see all other staff within a 10 mile radius of their longitude and latitude.
How can I achieve this?
Many thanks
Damian
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes its < and > thanks XzKto.
but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
Err:
yes its < and NOT > thanks XzKto.
but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
yes its < and NOT > thanks XzKto.
but C*C is again a Constant :) so why give that burden to the sql server :) we can calculate and give it directly. if you dont like C we can make it D :) where D = C * C :)
ASKER
That's great code people, I'm trying to add to my form. Could anyone show me how to have a drop down in php with say:
10
20
30
40
50
(which is the miles), and then integrate this into my SQL search?
Many thanks
Damian
10
20
30
40
50
(which is the miles), and then integrate this into my SQL search?
Many thanks
Damian
Following is an approx code:
I have left the db connection and fetch for you. You also have to fill the actual user's co-ordinates:
I have left the db connection and fetch for you. You also have to fill the actual user's co-ordinates:
<?php
if(isset($_GET['submit'])){
$miles = $_GET['miles'];
$miles_latlon = 0.00020882756 * $miles * $miles; //Approx Value
$user_longitude = 41;//'fill actual
$user_latitude = -71;//fill actual
$sql='SELECT username
FROM `USERS`
WHERE ((longitude-'.$user_longitude.')*(longitude-'.$user_longitude.')
+
(latitude-'.$user_latitude.')*(latitude-'.$user_latitude.'))<'. $miles_latlon).';';
//Connect to DB and fetch result
}
?>
<form method='get">
<select name="miles">
<option value="10"></option>
<option value="20"></option>
<option value="30"></option>
<option value="40"></option>
<option value="50"></option>
</select>
<input type="submit" name="submit" value="submit"/>
</form>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This article teaches the principles, along with tested code samples:
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
ASKER
Hi,
I have added this to my code:
$sql='SELECT *
FROM `members`
WHERE ((longitude-'.$user_longit ude.')*(lo ngitude-'. $user_long itude.')
+
(latitude-'.$user_latitude .')*(latit ude-'.$use r_latitude .'))<'.($m iles_latlo n).';';
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo $sql;
However, in the echo of the SQL return, this is all I see:
SELECT * FROM `members` WHERE ((longitude--3.0134983)*(l ongitude-- 3.0134983) + (latitude-53.8306789)*(lat itude-53.8 306789))<0 .020882756 ;
It just contains the select statement with all my values? What I need it to do is list the people i.e. firstname.
What have I done wrong?
Regards,
Damian
I have added this to my code:
$sql='SELECT *
FROM `members`
WHERE ((longitude-'.$user_longit
+
(latitude-'.$user_latitude
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo $sql;
However, in the echo of the SQL return, this is all I see:
SELECT * FROM `members` WHERE ((longitude--3.0134983)*(l
It just contains the select statement with all my values? What I need it to do is list the people i.e. firstname.
What have I done wrong?
Regards,
Damian
See the comment on line 14:
//Connect to DB and fetch result
//Connect to DB and fetch result
ASKER
Ray,
That's what this line is for:
$sql='SELECT *
FROM `members`
WHERE ((longitude-'.$user_longit ude.')*(lo ngitude-'. $user_long itude.')
+
(latitude-'.$user_latitude .')*(latit ude-'.$use r_latitude .'))<'.($m iles_latlo n).';';
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo $sql;
???
That's what this line is for:
$sql='SELECT *
FROM `members`
WHERE ((longitude-'.$user_longit
+
(latitude-'.$user_latitude
if (!mysql_query($sql))
{
die('Error: ' . mysql_error());
}
echo $sql;
???
this link give info on how to connect to db and fetch the results
http://www.webcheatsheet.com/PHP/connect_mysql_database.php
http://www.webcheatsheet.com/PHP/connect_mysql_database.php
The first code snippet in the article shows how to connect, select and query a data base.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
See lines 75 through 90.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_4276-What-is-near-me-Proximity-calculations-using-PHP-and-MySQL.html
See lines 75 through 90.
Hai,
Just find the attachment and go throw on "GoogleApi.class"
you can get different ideas to make the map more better,
GoogleMapAPI() function is the main one.
GoogleMapAPI.class.php
Just find the attachment and go throw on "GoogleApi.class"
you can get different ideas to make the map more better,
GoogleMapAPI() function is the main one.
GoogleMapAPI.class.php
ASKER
liveaspankaj,
There seems to be an issue with your code...... if I take the SQL statement:
$sql='SELECT username
FROM `USERS`
WHERE ((longitude-'.$user_longit ude.')*(lo ngitude-'. $user_long itude.')
+
(latitude-'.$user_latitude .')*(latit ude-'.$use r_latitude .'))<'. $miles_latlon).';';
it doesn't return any values, so I manually entered the values for the longitude and latitude, as well as the distance on the end and it works, both in SQL directly, and in PHP, however as soon as I change the :
'.$user_latitude.'
it starts to fail. I can confirm this variable does have a value.
How can I output the SQL string to see what's being formed?
Thanks
There seems to be an issue with your code...... if I take the SQL statement:
$sql='SELECT username
FROM `USERS`
WHERE ((longitude-'.$user_longit
+
(latitude-'.$user_latitude
it doesn't return any values, so I manually entered the values for the longitude and latitude, as well as the distance on the end and it works, both in SQL directly, and in PHP, however as soon as I change the :
'.$user_latitude.'
it starts to fail. I can confirm this variable does have a value.
How can I output the SQL string to see what's being formed?
Thanks
If a map is what the asker wants, this article teaches how to do that.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3350-Using-the-Google-Maps-API-in-PHP.html
An example of using that technology is on this page:
http://landonbaseball.com/site_visitors.php
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_3350-Using-the-Google-Maps-API-in-PHP.html
An example of using that technology is on this page:
http://landonbaseball.com/site_visitors.php
"How can I output the SQL string to see what's being formed?"
If the SQL string is in a varible named, for example, $sql you can use this:
var_dump($sql);
If the SQL string is in a varible named, for example, $sql you can use this:
var_dump($sql);
ASKER
Thanks very much everyone, all sorted now! Great work.....
1. read the values of $lat and $lon from the user where he is.
2. make the sql like below.
3. 'C' is the constant value which you can calculate and put directly. How?
i. find the relation how lat,lon actuate in real distances, or
ii. take lat,lon of two points on map at 10 miles apart and calculate:
C = ((lat1 - lat2) * (lat1 - lat2) + (lon1 - lon2) * (lon1 - lon2);
SELECT * from users WHERE ((lat - $lat) * (lat - $lat) + (lon - $lon) * (lon - $lon)) > C;
i belive you can take forward from here