Read MySQL Database

[davefootball123]

I have a script that compares clicked latitude longitude to the nearest latitude longitude point in an array. The array is seen below. What is seen there is the id...and then the after array in order it is the cities forecast ID, latitude, longitude. This works fine however I have the same information stored in a MySQL database now. The rows are id ....forecast_id ...lat and lon. Is there anyway possible to get the MySQL row data and have it in the same form as the array below so that my script can compare the latitude longitude datas?

Any help would be awesome.

 

Thanks again, Dave.

$items = array(
   '0' => array('s0000549','43.24','-79.99'),
'1' => array('s0000692','43.06','-79.11'),
'2' => array('s0000728','44.57','-80.93'),
   '3' => array('s0000571','43.54','-80.23')
);

My whole script can be seen here.

<?php



$lat = $_GET["lat"];
$lon = $_GET["lon"];



$ref = array($lat, $lon);




function distance($a, $B)
{
   list($lat1, $lon1) = $a;
   list($lat2, $lon2) = $b;


   $theta = $lon1 - $lon2;
   $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
   $dist = acos($dist);
   $dist = rad2deg($dist);
   $miles = $dist * 60 * 1.1515;
   return $miles;
}
$items = array(
   '0' => array('s0000549','43.24','-79.99'),
'1' => array('s0000692','43.06','-79.11'),
'2' => array('s0000728','44.57','-80.93'),
   '3' => array('s0000571','43.54','-80.23')
);


$distances = array_map(function($item) use($ref) {
   $a = array_slice($item, -2);
   return distance($a, $ref);
}, $items);


asort($distances);


$location = $items[key($distances)][0];




$url = 'http://dd.weatheroffice.gc.ca/citypage_weather/xml/ON/'.$location.'_e.xml';



$xml = simplexml_load_file($url);
$file_contents = file_get_contents($url);
$none = '<warnings/>';


if(strpos($file_contents, $none) !== FALSE){


       echo 'NO WATCHES OR WARNINGS IN EFFECT';


}
else
{





foreach ($xml->warnings as $warning) {


$warntext = $warning->event->attributes()->description.'';


echo $warntext;


    }






}



?>

[davefootball123]

I managed to get the database query working. The code below is working well...however it is only getting the data for the 1st forecast location in the database. I need it to loop through the whole database to compare lat/lon. Any ideas?

<?php



$lat = $_GET["lat"];
$lon = $_GET["lon"];


$error = "Couldn't Connect";


$connect = mysql_connect("localhost", "sowx_wxweb", "davefootball123");
mysql_select_db("sowx_wxweb") or die($error);


$data = mysql_query("SELECT * FROM ontario");


$forecast = mysql_fetch_row($data);




$ref = array($lat, $lon);




function distance($a, $B)
{
   list($lat1, $lon1) = $a;
   list($lat2, $lon2) = $b;


   $theta = $lon1 - $lon2;
   $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
   $dist = acos($dist);
   $dist = rad2deg($dist);
   $miles = $dist * 60 * 1.1515;
   return $miles;
}
$items = array(
   $forecast[0] => array( $forecast[3] , $forecast[4], $forecast[5])

);


$distances = array_map(function($item) use($ref) {
   $a = array_slice($item, -2);
   return distance($a, $ref);
}, $items);


asort($distances);


$location = $items[key($distances)][0];

[davefootball123]

Just did a bit more work and now have all the data in the database showing up with the code seen below. I'm getting there...however instead of comparing latitude and longitude data it just sets the location as the last one in the database. My goal is to have the google map clicked latitude and longitude compared to the database to get the closest location. I already have the google maps part done...and the databse done. Just need to finish this now.

 

Any help would be awesome, Dave

<?php



$lat = $_GET["lat"];
$lon = $_GET["lon"];


$error = "Couldn't Connect";


$connect = mysql_connect("localhost", "sowx_wxweb", "davefootball123");
mysql_select_db("sowx_wxweb") or die($error);


$data = mysql_query("SELECT * FROM ontario");


$forecast = mysql_fetch_row($data);


while ($forecast = mysql_fetch_array($data)) {


$place = $forecast['forecast_id'];

$city = $forecast['name'];





$lat = $forecast['lat'];





$lon = $forecast['lon'];




$id = $forecast['id'];




}





$ref = array($lat, $lon);




function distance($a, $B)
{
list($lat1, $lon1) = $a;
list($lat2, $lon2) = $b;


$theta = $lon1 - $lon2;
$dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
$dist = acos($dist);
$dist = rad2deg($dist);
$miles = $dist * 60 * 1.1515;
return $miles;
}
$items = array(
$id => array($place, $lat, $lon)

);


$distances = array_map(function($item) use($ref) {
$a = array_slice($item, -2);
return distance($a, $ref);
}, $items);


asort($distances);


$location = $items[key($distances)][0];

Edited February 15, 2013 by davefootball123

[davefootball123]

I solved the issue.

<?php
$error = "Couldn't Connect";
$lat = $_GET["lat"];
$lon = $_GET["lon"];
$connect = mysql_connect("localhost", "sowx_wxweb", "davefootball123");
mysql_select_db("sowx_wxweb") or die($error);



$sql = "SELECT  * ," . "(@distance:=(3963*ACOS(SIN(a.lat/57.3)*SIN($lat/57.3)+COS(a.lat/57.3)*COS($lat/57.3)*COS($lon/57.3 - a.lon/57.3)))) AS distance FROM ontario  as a " . 
                                                  " ORDER BY distance limit 0,1";


$data = mysql_query($sql);


$forecast = mysql_fetch_row($data);





echo $forecast[3];


?>

[Barand]

If you have a db table containing

 

forecast_id | lat | lon

 

then this query will find the nearest to the input lat and long

 

$lat = floatval($_GET["lat"]);
$lon = floatval($_GET["lon"]);

$sql = "SELECT forecast_id,
   3963.0 * ACOS(SIN(RADIANS(lat)) * SIN(RADIANS($lat)) +  COS(RADIANS(lat)) *
   COS(RADIANS($lat)) * COS(RADIANS(lon - $lon))) as dist
   FROM mytablename
   ORDER BY dist
   LIMIT 1";

[davefootball123]

If you have a db table containing

 

forecast_id | lat | lon

 

then this query will find the nearest to the input lat and long

 

$lat = floatval($_GET["lat"]);
$lon = floatval($_GET["lon"]);

$sql = "SELECT forecast_id,
3963.0 * ACOS(SIN(RADIANS(lat)) * SIN(RADIANS($lat)) + COS(RADIANS(lat)) *
COS(RADIANS($lat)) * COS(RADIANS(lon - $lon))) as dist
FROM mytablename
ORDER BY dist
LIMIT 1";

Thanks Barand. I managed to figure out the script myself this time.