prepare( " SELECT id, attribution, title, lat, lon, imageURL, line4, line3, line2, type, dimension, (((acos(sin((:lat1 * pi() / 180)) * sin((lat * pi() / 180)) + cos((:lat2 * pi() / 180)) * cos((lat * pi() / 180)) * cos((:long - lon) * pi() / 180)) ) * 180 / pi()) * 60 * 1.1515 * 1.609344 * 1000) as distance FROM POI_Table HAVING distance < :radius ORDER BY distance ASC LIMIT 0, 50 " ); // PDOStatement::bindParam() binds the named parameter markers to the specified parameter values. $sql->bindParam( ':lat1', $value['lat'], PDO::PARAM_STR ); $sql->bindParam( ':lat2', $value['lat'], PDO::PARAM_STR ); $sql->bindParam( ':long', $value['lon'], PDO::PARAM_STR ); $sql->bindParam( ':radius', $value['radius'], PDO::PARAM_INT ); // Use PDO::execute() to execute the prepared statement $sql. $sql->execute(); // Iterator for the response array. $i = 0; // Use fetchAll to return an array containing all of the remaining rows in the result set. // Use PDO::FETCH_ASSOC to fetch $sql query results and return each row as an array indexed by column name. $pois = $sql->fetchAll(PDO::FETCH_ASSOC); /* Process the $pois result */ // if $pois array is empty, return empty array. if ( empty($pois) ) { $response["hotspots"] = array (); }//if else { // Put each POI information into $response["hotspots"] array. foreach ( $pois as $poi ) { // If not used, return an empty actions array. $poi["actions"] = array(); // Store the integer value of "lat" and "lon" using predefined function ChangetoIntLoc. $poi["lat"] = ChangetoIntLoc( $poi["lat"] ); $poi["lon"] = ChangetoIntLoc( $poi["lon"] ); // Change to Int with function ChangetoInt. $poi["type"] = ChangetoInt( $poi["type"] ); $poi["dimension"] = ChangetoInt( $poi["dimension"] ); // Change to demical value with function ChangetoFloat $poi["distance"] = ChangetoFloat( $poi["distance"] ); // Put the poi into the response array. $response["hotspots"][$i] = $poi; $i++; }//foreach }//else return $response["hotspots"]; }//Gethotspots /*** Main entry point ***/ /* Pre-define connection to the MySQL database, please specify these fields.*/ $dbhost = "localhost"; $dbdata = "database_name"; $dbuser = "database_username"; $dbpass = "database_password"; /* Put parameters from GetPOI request into an associative array named $value */ // Put needed parameter names from GetPOI request in an array called $keys. $keys = array( "layerName", "lat", "lon", "radius" ); // Initialize an empty associative array. $value = array(); try { // Retrieve parameter values using $_GET and put them in $value array with parameter name as key. foreach( $keys as $key ) { if ( isset($_GET[$key]) ) $value[$key] = $_GET[$key]; else throw new Exception($key ." parameter is not passed in GetPOI request."); }//foreach }//try catch(Exception $e) { echo 'Message: ' .$e->getMessage(); }//catch try { /* Connect to MySQL server. We use PDO which is a PHP extension to formalise database connection. For more information regarding PDO, please see http://php.net/manual/en/book.pdo.php. */ // Connect to predefined MySQl database. $db = new PDO( "mysql:host=$dbhost; dbname=$dbdata", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); // set the error reporting attribute to Exception. $db->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_EXCEPTION ); /* Construct the response into an associative array.*/ // Create an empty array named response. $response = array(); // Assign cooresponding values to mandatory JSON response keys. $response["layer"] = $value["layerName"]; // Use Gethotspots() function to retrieve POIs with in the search range. $response["hotspots"] = Gethotspots( $db, $value ); // if there is no POI found, return a custom error message. if ( empty( $response["hotspots"] ) ) { $response["errorCode"] = 20; $response["errorString"] = "No POI found. Please adjust the range."; }//if else { $response["errorCode"] = 0; $response["errorString"] = "ok"; }//else /* All data is in $response, print it into JSON format.*/ // Put the JSON representation of $response into $jsonresponse. $jsonresponse = json_encode( $response ); // Declare the correct content type in HTTP response header. header( "Content-type: application/json; charset=utf-8" ); // Print out Json response. echo $jsonresponse; /* Close the MySQL connection.*/ // Set $db to NULL to close the database connection. $db=null; } catch( PDOException $e ) { echo $e->getMessage(); } ?>