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:

<?php
 
//connect to my database
 $sqlconnect=mysql_connect("localhost","__munged__","__munged__");
 if(!$sqlconnect)
     die(mysql_error());
 mysql_select_db("hyperian_track", $sqlconnect);
 
 
$fetched=mysql_query("SELECT districtname FROM location,district
 WHERE location.lat < district.startlat AND location.lat > district.endlat AND location.lng < district.startlng AND location.lng > district.endlng");
 
while($row = mysql_fetch_array($fetched))
{
    $result=$row[$districtname];
    return $result;
}

[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.

SELECT `districtname` FROM `location`,`district`
WHERE `location`.`lat` BETWEEN `district`.`startlat` AND `district`.`endlat` AND `location`.`lng` BETWEEN `district`.`startlng` AND `district`.`endlng`

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

AND `location`.`uid` = '{$uid}'

, 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/...nction_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?:

$fetched=mysql_query(
"SELECT `districtname` FROM `location`,`district`
WHERE `location`.`lat` BETWEEN `district`.`startlat` AND `district`.`endlat` AND `location`.`lng`
BETWEEN `district`.`startlng` AND `district`.`endlng");
 
 
while($row = mysql_fetch_array($fetched))
{
    $result=$row[$districtname];
    return $result;
 
}

[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:

if(! ($result = mysql_query("SELECT * FROM `location` WHERE `uid` = '{$_SESSION['uid']}' LIMIT 1")))
    throw new Exception(mysql_error());
 
$_SESSION = array_merge($_SESSION, mysql_fetch_assoc($result));
 

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:

if(! ($result = mysql_query("SELECT `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_assoc($result)))
    throw new Exception('District not found!');
$_SESSION['districtname'] = $row['districtname'];
 

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

[Rocky86]

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=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


$fetched=mysql_query("SELECT districtname FROM location,district
WHERE location.lat < district.startlat AND location.lat > district.endlat AND location.lng < district.startlng AND location.lng > district.endlng");

while($row = mysql_fetch_array($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=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


$fetched=mysql_query("SELECT districtname FROM location,district
WHERE location.lat < district.startlat AND location.lat > district.endlat AND location.lng < district.startlng AND location.lng > district.endlng");

while($row = mysql_fetch_array($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,district
WHERE (location.lat BETWEEN district.startlat AND district.endlat) AND (location.lng BETWEEN district.startlng 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

[Rocky86]

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

Hi do I done abit of modification to the code you state above wonder iszit correct

select districtname,uid from location,districts
where
lat >= startlat AND lat <= endlat
AND lng >= startlng AND lng <= endlng
AND uid = $lnUID
ORDER BY districtname ASC

Just curios what does "ORDER BY districtname ASC" mean?

[Rocky86]

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

[PHP]$fetched=mysql_query("SELECT districtname FROM location,district
WHERE (location.lat BETWEEN district.startlat AND district.endlat) AND (location.lng BETWEEN district.startlng AND district.endlng)");[/PHP]

Hi thx for the sugguestion but I don't think the BETWEEN function can be use since I am not taking the value between the startlat and endlat instead I am comparing the lat value with both startlat and endlat ^.^

[Rocky86]

This is the new code forget on include some code in:

SELECT districtname,uid from location,districts
WHERE
location.lat >= districts.startlat AND location.lat <= districts.endlat
AND location.lng >= districts.startlng AND location.lng <= districts.endlng
AND location.uid = $lnUID
ORDER BY districtname ASC



Just curios what does "ORDER BY districtname ASC" mean?
And why you never put like districts.startlat(column name follow by the name of the row) instead you just put startlat?

[code green]

Just curios what does "ORDER BY districtname ASC" mean?

It means the results in the recordset will be in alphanumeric order based on the values in districtname. ASC (which is default anyway) means they will be in ascending order ie. from A-Z rather than DESC which will produce Z-A.

And why you never put like districts.startlat(column name follow by the name of the row) instead you just put startlat?

Using districts.startlat is good practice. It avoids the annnoying 'ambiguous' (have I spelt this correct) warnings if you have startlat in more than one table. However if you are confident there is no ambiguouty (this is misspelt!) you can omit the table name

[Rocky86]

I been thinking If I use forigen key would it be more easier for me to do the searching? cause right now both my table does not have any forigen keys

[nathj]

I been thinking If I use forigen key would it be more easier for me to do the searching? cause right now both my table does not have any forigen keys

That's correct - foreign keys would make your life a lot easier here. If you need help after that give us all a shout and we'll try to help.

nathj

[pbmods]

Foreign keys are only available to InnoDB tables. Run this query in your MySQL client app to check your table's storage engine:

SHOW CREATE TABLE `district`\G

Note the '\G' instead of ';'

You're looking for this on the last line:

) ENGINE=MyISAM 
-- Or..
) ENGINE=InnoDB
 

You can't change the table's storage engine with ALTER TABLE; instead, you'll have to create a new table with 'ENGINE=InnoDB' after the closing parenthesis, copy the data, then delete the original table and finally rename the new table.

E.g.,:

CREATE TABLE `newDistrict` ( `districtno` serial, ... ) ENGINE=InnoDB;
INSERT INTO `newDistrict` SELECT * FROM `district`;
DROP TABLE `district`;
ALTER TABLE `newDistrict` RENAME TO `district`;
OPTIMIZE TABLE `district`;
 

[Rocky86]

Foreign keys are only available to InnoDB tables. Run this query in your MySQL client app to check your table's storage engine:

SHOW CREATE TABLE `district`\G

Note the '\G' instead of ';'

You're looking for this on the last line:

) ENGINE=MyISAM 
-- Or..
) ENGINE=InnoDB
 

You can't change the table's storage engine with ALTER TABLE; instead, you'll have to create a new table with 'ENGINE=InnoDB' after the closing parenthesis, copy the data, then delete the original table and finally rename the new table.

E.g.,:

CREATE TABLE `newDistrict` ( `districtno` serial, ... ) ENGINE=InnoDB;
INSERT INTO `newDistrict` SELECT * FROM `district`;
DROP TABLE `district`;
ALTER TABLE `newDistrict` RENAME TO `district`;
OPTIMIZE TABLE `district`;
 

hi from your reply do I have to do the step you mention to make a foreign key for both of my table?

[Rocky86]

Hi guy thx for the help manage to solve the issue with the table thing but right now I am having some trouble again this time below is the code:

[PHP]SELECT startlat,endlat,startlng,endlng FROM districts WHERE districtno=[/PHP]

as you can see from the statement above I wanted to select startlat,endlat,startlng,endlng from the districts table BASE on the first 2 digit of the districtno and the first 2 digit of the districtno is base on the postal number that user input and how do I do it using substring function? I know substring might be able to do it to put it simple example user key in 569903 which is the postal code then I will base on the 56 and return the startlat,endlat,startlng,endlng..

Any suggestion plss help me thx!

[pbmods]

Heya, Rocky.

PHP's substr() function will be a big help with that.

[Rocky86]

Heya, Rocky.

PHP's substr() function will be a big help with that.

Hi pbmods thx for reply but I don't noe how to go along with it mind to show me the code for it?

1.Do I need to create a variable to store the postal code?
2.How do I use the function to take just the first 2 value?

[kovik]

substr()

It has examples.

[Rocky86]

substr()

It has examples.

hi i check the example alreadx mostly is on time and date I find it hard to understand it is there any more easier link?

[Rocky86]

helo everyone plss help me check this statement using substring() function

[PHP]
$postal=substr($_REQUEST['postal'],2);
$A=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal");
[/PHP]

I wanted to just extract the first 2 value of the postal number example "563212" I just want "56" extract out so is this code correct? first I declare a new var name $postal to store the postal number enter by the user and take out first 2 number and I declare another var name $A to select the startlat,endlat,startlng,endlng FROM districts table where districno is = to the $postal which is the first 2 value in this case is 56 is my code correct plss help me thx a million lot!!

[nathj]

helo everyone plss help me check this statement using substring() function

[PHP]
$postal=substr($_REQUEST['postal'],2);
$A=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal");
[/PHP]

I wanted to just extract the first 2 value of the postal number example "563212" I just want "56" extract out so is this code correct? first I declare a new var name $postal to store the postal number enter by the user and take out first 2 number and I declare another var name $A to select the startlat,endlat,startlng,endlng FROM districts table where districno is = to the $postal which is the first 2 value in this case is 56 is my code correct plss help me thx a million lot!!

Hi,

That looks pretty good to me. I would make one slight change, just because I like things to be as obvious as possible in code:

[PHP]
$postal=substr($_REQUEST['postal'],0,2); //will return from 0 position 2 characters
$A=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal");
[/PHP]

I beleive that should work fine. Have a play around with it and see what you get.

nathj

[Rocky86]

hi thx for the reply just wanted to noe what does this $_REQUEST do?

[nathj]

hi thx for the reply just wanted to noe what does this $_REQUEST do?

In PHP you can use the GET or POST methods to pass values between pages. If you use a form with the post method then $_POST can be used to retreived the value, if use a form with the get method then the values can be retreived fro the query strng using $_GET. $_REQUEST will handle both of them and $_COOKIE.

Personally I think it's clearer to use the one that corresponds with where the dat is coming from.

For a more detailed description take a look at the W3Schools tutorial.

One further change I would make, that I have just thought of:
[PHP]

if(isset($_GET['postal'])) // assuemd the value is on the URL, otherwise use $_POST
{
$postal = substr($_GET['postal'],0,2);// get the first two characters
$qry = 'SELECT startlat, endlat, startlng, endlng FROM districts WHERE districtno = $postal');
$results=mysql_query($qry);
}
else
{
// you can add error trapping here if you want, or some alternative if the variable is not available.
}
[/PHP]

nathj

[kovik]

hi thx for the reply just wanted to noe what does this $_REQUEST do?

Just don't use it. It's a bad habit to get into.

[Rocky86]

hi ppl is me right now I am told to merge my code together and send it to actionscript any idea how to do it I am told to follow the example done by other ppl below is the code done by that person
[PHP]
$fetched=mysql_query("SELECT * FROM location");
while($row = mysql_fetch_array($fetched))
{
$temp[]=$row;
}
foreach($temp as $extract)
foreach($extract as $key=>$info)
{
if(!is_numeric($key)) //somehow it returns number & name of key.so take 1. $coordinates[$key]=$info;
foreach($coordinates as $key=>$extract)
$report=$report.$key."=".$extract."&";
echo $report;
[/PHP]

I don't understand what the code is doing and how to join them up
below is the code I have now and is required to follow the code above to join them up and send it to actionscripts

[PHP]
$postal=substr($_GET['postal'],0,2); //will return from 0 position 2 characters

$resultpostal=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal"); //get startlat,endlat,startlng,endlng of employee location

$resultname=mysql_query("SELECT uid,uname FROM location,districts
WHERE location.lat < districts.startlat AND location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng");

echo("resultpostal" + "resultname");
}
[/PHP]

How do I join the code of mine together and send it to actionscript plss help me thx~

[pbmods]

Just don't use it [$_REQUEST]. It's a bad habit to get into.

You'll have to explain that one to me. I use it all the time; I use $_SESSION when I really care about where the data comes from....

[Rocky86]

Any ppl can help with my problem?

[pbmods]

Heya, Rocky.

Tell whoever gave you that example code that next time, he should use mysql_fetch_assoc() instead of mysql_fetch_array()

How are you sending the results to actionscript? Do you want to output an XML-formatted document for your Flash object to parse? Do you want to pass the variables as arguments in the query string? Do you want your script to output the actionscript itself?

[Rocky86]

Heya, Rocky.

Tell whoever gave you that example code that next time, he should use mysql_fetch_assoc() instead of mysql_fetch_array()

How are you sending the results to actionscript? Do you want to output an XML-formatted document for your Flash object to parse? Do you want to pass the variables as arguments in the query string? Do you want your script to output the actionscript itself?

Hi what I want is pass the variables as arguments in the query string

[nathj]

Hi what I want is pass the variables as arguments in the query string

To pass the variables in the query string on PHP simply add them on the end of the url:
www.mydomain.com/index.php?pageId=1

Then on index.php the following code will get that value for you:

<?php
if (isset($_GET['pageId']))
{
$lnPageID = $_GET['pageId'];
//use the variable however you want
}
else
{
echo 'The query string was not set properly';
// or some other form of exception code
}
?>
 

If you wish to pass the values from a form to another page in the query string this is done by setting the form action to 'get'. However, it is more advisable to use 'post' in a form situation. Then the in the code above you would replace $_GET with $_POST.

You can use $_REQUEST for either of these but it is not advisable as you have no idea then when reading the code where the data came from. Using the more specific $_POST or $_GET enables you to tell where the data came from. It makes life a lot easier for you now and in the future.

Alternatively you may wish to use $_SESSION. There are may discussions on this on this forum. But check out the manual for the basics

I hope this helped.
Cheers
nathj

[Rocky86]

hi I manage to come out with my php code pls help check iszit correct
[PHP]
<?php
$coordinates=array();
$report="";
$temp="";


$sqlconnect=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if(isset($_GET['postal'])) //check if is true
{

$postal=substr($_GET['postal'],0,2); //will return from 0 position 2 characters

$resultpostal=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal"); //get startlat,endlat,startlng,endlng of employee location

while($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}

foreach($temp as $extract)
foreach($extract as $key=>$info)
{
if(!is_numeric($key))
$coordinates[$key]=$info; //detect if what you extracted from $temp is not numeric and store it in $coordinates
}

foreach($coordinates as $key=>$extract) // store the values in the $coordinates and $extract into $report and display them in the screen....

$report=$report.$key."=".$extract."&";


echo $report;


$resultname=mysql_query("SELECT uid,uname FROM location,districts
WHERE location.lat < districts.startlat AND location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng");

while($row = mysql_fetch_array($resultname))
{
$temp[]=$row;
}

foreach($temp as $extract)
foreach($extract as $key=>$info)
{
if(!is_numeric($key))
$coordinates[$key]=$info;
}

foreach($coordinates as $key=>$extract)
$report=$report.$key."=".$extract."&";


echo $report;

}//end of if loops

else
{
echo ("error");
}


mysql_close($con);

[/PHP]

My Objective is:
In php join them up then send it actionscript

actionscript will seperate them out

[nathj]

Hi Rocky86,

I have read over this quickly and it looks okay, there is lots of array parsing going on. The best way to find out if it is working or not is to run the code. Then work through any issues that you have.

If you come up against anything you can't resolve then post the specifics. I'll do what I can to help out.

Cheers
nathj

[Rocky86]

Hi Rocky86,

I have read over this quickly and it looks okay, there is lots of array parsing going on. The best way to find out if it is working or not is to run the code. Then work through any issues that you have.

If you come up against anything you can't resolve then post the specifics. I'll do what I can to help out.

Cheers
nathj

hihi nathj thx for taking the effort to help me out man but do you know how do I send this php code to actionscripts and use the actionscript to separate the code out?

do I need to create function on actionscript to take in the php code?

[nathj]

hihi nathj thx for taking the effort to help me out man but do you know how do I send this php code to actionscripts and use the actionscript to separate the code out?

do I need to create function on actionscript to take in the php code?

Unfortunatley thats where my ability dies on this one. I have never done this and am not even sure what it is. I assume its Flash we're talking about? I have only ever done really basic stuff in flash - like image transitions.

Sorry I can't be any more help on this.

Cheers
nathj

[Rocky86]

Unfortunatley thats where my ability dies on this one. I have never done this and am not even sure what it is. I assume its Flash we're talking about? I have only ever done really basic stuff in flash - like image transitions.

Sorry I can't be any more help on this.

Cheers
nathj

Oh anyway nathj you have been a great help thx alot!!

[pbmods]

Heya, Rocky.

The way I would do it would be to load the file in your Actionscript.

[Rocky86]

hi if I do not want my php to check for numeric so do I just remove the numeric code part?
[PHP]
<?php
$coordinates=array();
$report="";
$temp="";


$sqlconnect=mysql_connect("localhost","hyperian_tr ack","gsmtrack");
if(!$sqlconnect)
die(mysql_error());
mysql_select_db("hyperian_track", $sqlconnect);


if(isset($_GET['postal'])) //check if is true
{

$postal=substr($_GET['postal'],0,2); //will return from 0 position 2 characters

$resultpostal=mysql_query("SELECT startlat,endlat,startlng,endlng FROM districts
WHERE districtno = $postal"); //get startlat,endlat,startlng,endlng of employee location

while($row = mysql_fetch_array($resultpostal))
{
$temp[]=$row; // store each record in an array
}


foreach($temp as $key=>$extract) // store the values in the $coordinates and $extract into $report and display them in the screen....

$report=$report.$key."=".$extract."&";


echo $report;


$resultname=mysql_query("SELECT uid,uname FROM location,districts
WHERE location.lat < districts.startlat AND location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng");

while($row = mysql_fetch_array($resultname))
{
$temp[]=$row;
}



foreach($temp as $key=>$extract)
$report=$report.$key."=".$extract."&";


echo $report;

}//end of if loops

else
{
echo ("error");
}


mysql_close($con);




[/PHP]
Is my code correct? telling it to print out the startlng,endlng,startlat and endlat if districtno is = to postal And print out the userid,username if location.lat > districts.endlat AND location.lng < districts.startlng
AND location.lng > districts.endlng store it into and array name temp and $extract into $report and display them in the screen?