Searching for a location in the database by latitude/longitude.

[Rocky86]

Hi brothers I am new here need help with the php programing using sql basically I am given a task to create a php code that is able to query the database on some free hosting web which I alreadx have the database done right now I am trying to query the database and I have no idea how to do it I wanted to find the value between the Latitude top, latitude bottom and longtitude top and longtitude bottom if it is between it will return the districtname I alreadx have the table done except the coding this is what I currently have:

[code=php]<?php

//connect to my database
$sqlconnect=mys ql_connect("loc alhost","__mung ed__","__munged __");
if(!$sqlconnect )
die(mysql_error ());
mysql_select_db ("hyperian_trac k", $sqlconnect);


$fetched=mysql_ query("SELECT districtname FROM location,distri ct
WHERE location.lat < district.startl at AND location.lat > district.endlat AND location.lng < district.startl ng AND location.lng > district.endlng ");

while($row = mysql_fetch_arr ay($fetched))
{
$result=$row[$districtname];
return $result;
}[/code]

[Please use CODE tags when posting source code. Thanks! --pbmods]


Is my code correct? I first time using php so is unsure please guide me thx alot brothers!!

[pbmods]

Changed thread title to better describe the problem.

Heya, Rocky.

What does your code produce? Post an example.
What is it supposed to produce? Post an example.
Are you getting an error? Post the error message.

[Rocky86]

This is the table require to be use

District Table

districtno	districtname	startlat	endlat	startlng	endlng  
 56			Ang Mo Kio		1.394		1.360	103.820		103.877 
 57			Bishan			1.363		1.343	103.820		103.848

Location Table

uid	uname			cellid		lat		lng     
1	Peter Collins	201828471	1.378	103.807 
2	Mary Hopenson	3847297		1.298	103.853

If the lat column and the lng column in the Location table is in between the startlat,endlat and startlng, endlng columns respectively, then return the districtname column, in the District Table

I am require to create a php file using SQL to do it so the php can connect to the database and retrieve the data my project is on employee tracking system using GPS to track the employee location by searching through postal code or area thx for the help!

[pbmods]

Heya, Rocky86.

You can probably simplify your query a little bit by using the BETWEEN operator.

[code=mysql]SELECT `districtname` FROM `location`,`dis trict`
WHERE `location`.`lat ` BETWEEN `district`.`sta rtlat` AND `district`.`end lat` AND `location`.`lng ` BETWEEN `district`.`sta rtlng` AND `district`.`end lng`[/code]

This will return every district that 'contains' the coordinates for a location. If you want to return the district for a specific employee, you'll need to add [code=mysql]AND `location`.`uid ` = '{$uid}'[/code], where $uid is set to the ID number of the employee you want to locate.

[Rocky86]

hi thx for the reply just curious if I were to use the Between operator is there any restriction on the data type for example my startlat,endlat ,startlng and endlng is all in decimal(20,20) or do I need to change the format to varchar(20)
for the between operator to works? since I am comparing it in integer?

[pbmods]

Heya, Rocky.

just curious if I were to use the Between operator is there any restriction on the data type for example my startlat,endlat ,startlng and endlng is all in decimal(20,20) or do I need to change the format to varchar(20)
for the between operator to works? since I am comparing it in integer?

You can use BETWEEN on just about any data type:

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_between

[Rocky86]

thx for yr help brother btw If I want to loop through the all row of the table to find the correct district name and return just the district name if manage to find it then the code I post here iszit correct?:
[code=php]
$fetched=mysql_ query(
"SELECT `districtname` FROM `location`,`dis trict`
WHERE `location`.`lat ` BETWEEN `district`.`sta rtlat` AND `district`.`end lat` AND `location`.`lng `
BETWEEN `district`.`sta rtlng` AND `district`.`end lng");


while($row = mysql_fetch_arr ay($fetched))
{
$result=$row[$districtname];
return $result;

}[/code]

[Please use CODE tags when posting source code. Thanks! --pbmods]

[pbmods]

Please do not bump your posts. Thanks!

[pbmods]

Heya, Rocky.

Your code is all over the place. Let's simplify this.

If we have a User registered in the database, and we know where he is located, then we can load that information first to make things a lot easier.

My recommendation is to load the User's location data when the User logs in:
[code=php]
if(! ($result = mysql_query("SE LECT * FROM `location` WHERE `uid` = '{$_SESSION['uid']}' LIMIT 1")))
throw new Exception(mysql _error());

$_SESSION = array_merge($_S ESSION, mysql_fetch_ass oc($result));
[/code]

This is a quick-n-dirty 'load a row from the database into the session'.

Once you have the User's location registered in the session, it becomes very easy to determine the User's current district:

[code=php]
if(! ($result = mysql_query("SE LECT `districtname` FROM `district` WHERE ('{$_SESSION['lat']}' BETWEN `startlat` AND `endlat`) AND ('{$_SESSION['lng']}' BETWEEN `startlng` AND `endlng`) LIMIT 1")))
throw new Exception(mysql _error());

if(empty($row = mysql_fetch_ass oc($result)))
throw new Exception('Dist rict not found!');
$_SESSION['districtname'] = $row['districtname'];
[/code]

Not sure if the above query properly respects indexes, but it should do what you need.

[Rocky86]

Need Help PHP + MYSQL I am stuck!!

Hi people I am so in help with the php and mysql code
My objective is to display the districtname from the district table if

Condition 1) the values under the lat columns from the location table fall in between the values of startlat,endlat columns in the districts table

AND

Condition 2) the values under the lng column from the location table falls in between startlng,endlng in the districts table

Below is the 2 table I am using:

District Table
screenshot

Location Table
screenshot

Below is the php code I manage to think of so far but I unsure how to carry on with it please help me

[PHP]<?php
$sqlconnect=mys ql_connect("loc alhost","hyperi an_track","gsmt rack");
if(!$sqlconnect )
die(mysql_error ());
mysql_select_db ("hyperian_trac k", $sqlconnect);


$fetched=mysql_ query("SELECT districtname FROM location,distri ct
WHERE location.lat < district.startl at AND location.lat > district.endlat AND location.lng < district.startl ng AND location.lng > district.endlng ");

while($row = mysql_fetch_arr ay($fetched))
{
$result=$row[$districtname];
echo $result;
}


[/PHP]

I am using PHP and MYSQL

[bonski]

Hi people I am so in help with the php and mysql code
My objective is to display the districtname from the district table if

Condition 1) the values under the lat columns from the location table fall in between the values of startlat,endlat columns in the districts table

AND

Condition 2) the values under the lng column from the location table falls in between startlng,endlng in the districts table

Below is the 2 table I am using:

District Table
screenshot

Location Table
screenshot

Below is the php code I manage to think of so far but I unsure how to carry on with it please help me

[PHP]<?php
$sqlconnect=mys ql_connect("loc alhost","hyperi an_track","gsmt rack");
if(!$sqlconnect )
die(mysql_error ());
mysql_select_db ("hyperian_trac k", $sqlconnect);


$fetched=mysql_ query("SELECT districtname FROM location,distri ct
WHERE location.lat < district.startl at AND location.lat > district.endlat AND location.lng < district.startl ng AND location.lng > district.endlng ");

while($row = mysql_fetch_arr ay($fetched))
{
$result=$row[$districtname];
echo $result;
}


[/PHP]

I am using PHP and MYSQL

not sure but this but give it a try... ^___^

[PHP]$fetched=mysql_ query("SELECT districtname FROM location,distri ct
WHERE (location.lat BETWEEN district.startl at AND district.endlat ) AND (location.lng BETWEEN district.startl ng AND district.endlng )");[/PHP]

[ak1dnar]

Where is the the foriegn key ? for those table.

[nathj]

If you are after the district name based on the uid in location
this is possible with your data structure but it would be easier with
a correctly defined relationship.
however, assuiming $lnUID is a variable for the user you can
find out which district they are in as follows:

select a.districtname b.uid from district a, uid b
where
lat >= startlat AND lat <= endlat
AND lng >= strtlng AND lng <= endlng
AND uid = $lnUID
ORDER BY districtname ASC

I note the use of BETWEEN in the other sample, I didn't know about this until I read it, so thanks for that.

Nathan

[pbmods]

Merged duplicate threads (Rocky, please use RELEVANT titles when you post new threads; thanks!).

[Rocky86]

Where is the the foriegn key ? for those table.

do I reallx need a foriegn key? for now both of my table do not have a foriegn key