Give it a try with this code. I couldn't test it, but if you go step by step and have patience it will work. If you have errors or encounter problems, give us feedback. It may be, that the parameter for LIMIT in sql statement will not work from the first try. Just tell us. Also, I used the following code for the Haversine formula and rectangle calculation: Reverse Geocoding with it loaded into MySQL database?, because it's almost identical with yours.
Some recommendations:
- Try to move to OOP.
- Always apply exception handling and activate error reporting and
handling. I gave you a general view by throwing only
Exception.
Normally you should learn how to throw and handle the SPL
(Standard PHP Library) types of it too.
- Always use prepared statements (I know you're already doing this ;-).
- Always read in the PHP Manual what PHP functions are returning so that you can correctly apply handle cases (exceptions, bools, etc).
Resources:
Good luck!
Haversine formula with PDO prepared statements & exception handling
index.php (main page):
<?php
require_once 'configs.php';
require_once 'functions.php';
require_once 'geolocationFunctions.php';
// Activate error reporting (only on development).
activateErrorReporting();
try {
// Create db connection.
$connection = createConnection(
MYSQL_HOST
, MYSQL_DATABASE
, MYSQL_USERNAME
, MYSQL_PASSWORD
, MYSQL_PORT
, MYSQL_CHARSET
);
$cardsInRadius = getCardsInRadius($connection, $word, $longitude, $latitude, $radius, $limit = 100);
// For testing purposes.
printData($cardsInRadius, TRUE);
closeConnection($connection);
} catch (PDOException $pdoException) {
// On development.
printData($pdoException, TRUE);
// On production.
// echo $pdoException->getMessage();
exit();
} catch (Exception $exception) {
// On development.
printData($exception, TRUE);
// On production.
// echo $exception->getMessage();
exit();
}
geolocationFunctions.php (included in main page):
<?php
/*
* ---------------------
* Geolocation functions
* ---------------------
*/
/**
* Search the for a keyword and return data located within given radius.
*
* @param PDO $connection Connection instance.
* @param string $word Keyword to lookup.
* @param double $longitude Longitude value to lookup.
* @param double $latitude Latitude value to lookup.
* @param integer $radius Distance radius (in miles) having lat/long as center point.
* @param integer $limit [optional] Number of records to return.
* @throws Exception
*/
function getCardsInRadius($connection, $word, $longitude, $latitude, $radius, $limit = 100) {
/*
* Create a rectangle in which the circle with the given radius will be defined.
* >> 1° of latitude ~= 69 miles
* >> 1° of longitude ~= cos(latitude) * 69
*/
$rectLong1 = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
$rectLong2 = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
$rectLat1 = $latitude - ($radius / 69);
$rectLat2 = $latitude + ($radius / 69);
// Approximate the circle inside the rectangle.
$distance = sprintf('3956 * 2 * ASIN(SQRT(POWER(SIN((%s - latitudeLocateDB) * pi()/180 / 2), 2) + COS(%s * pi()/180) * COS(latitudeLocateDB * pi()/180) * POWER(SIN((%s - longitudeLocateDB) * pi()/180 / 2), 2) ))'
, $latitude
, $latitude
, $longitude
);
// Sql statement.
$sql = sprintf('SELECT
*,
%s AS distance
FROM carddbtable
WHERE
(
businessNameDB = :businessNameDB
OR lastNameDB = :lastNameDB
OR firstKeywordDB = :firstKeywordDB
OR secondKeywordDB = :secondKeywordDB
OR thirdKeywordDB = :thirdKeywordDB
OR fourthKeywordDB = :fourthKeywordDB
OR fithKeywordDB = :fithKeywordDB
)
AND longitudeLocateDB BETWEEN :rectLong1 AND :rectLong2
AND latitudeLocateDB BETWEEN :rectLat1 AND :rectLat2
HAVING distance < :distance
ORDER BY distance
LIMIT :limit'
, $distance
);
// Prepare and check sql statement (returns PDO statement).
$statement = $connection->prepare($sql);
if (!$statement) {
throw new Exception('The SQL statement can not be prepared!');
}
// Bind values to sql statement parameters.
$statement->bindValue(':businessNameDB', $word, getInputParameterDataType($word));
$statement->bindValue(':lastNameDB', $word, getInputParameterDataType($word));
$statement->bindValue(':firstKeywordDB', $word, getInputParameterDataType($word));
$statement->bindValue(':secondKeywordDB', $word, getInputParameterDataType($word));
$statement->bindValue(':thirdKeywordDB', $word, getInputParameterDataType($word));
$statement->bindValue(':fourthKeywordDB', $word, getInputParameterDataType($word));
$statement->bindValue(':fithKeywordDB', $word, getInputParameterDataType($word));
$statement->bindValue(':rectLong1', $rectLong1, getInputParameterDataType($rectLong1));
$statement->bindValue(':rectLong2', $rectLong2, getInputParameterDataType($rectLong2));
$statement->bindValue(':rectLat1', $rectLat1, getInputParameterDataType($rectLat1));
$statement->bindValue(':rectLat2', $rectLat2, getInputParameterDataType($rectLat2));
$statement->bindValue(':distance', $radius, getInputParameterDataType($radius));
$statement->bindValue(':limit', $limit, getInputParameterDataType($limit));
// Execute and check PDO statement.
if (!$statement->execute()) {
throw new Exception('The PDO statement can not be executed!');
}
// Fetch person details.
$fetchedData = $statement->fetchAll(PDO::FETCH_ASSOC);
if (!$fetchedData) {
throw new Exception('Fetching data failed!');
}
return $fetchedData;
}
configs.php (included in main page):
<?php
/*
* ----------------
* Database configs
* ----------------
*/
define('MYSQL_HOST', '...');
define('MYSQL_PORT', '3306');
define('MYSQL_DATABASE', '...');
define('MYSQL_CHARSET', 'utf8');
define('MYSQL_USERNAME', '...');
define('MYSQL_PASSWORD', '...');
functions.php (included in main page):
<?php
/*
* ---------------------
* Data access functions
* ---------------------
*/
/**
* Create a new db connection.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $username Username.
* @param string $password Password.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @param array $options [optional] Driver options.
* @return PDO Db connection.
*/
function createConnection($host, $dbname, $username, $password, $port = '3306', $charset = 'utf8', $options = array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_PERSISTENT => true,
)) {
$dsn = getDsn($host, $dbname, $port, $charset);
$connection = new PDO($dsn, $username, $password);
foreach ($options as $key => $value) {
$connection->setAttribute($key, $value);
}
return $connection;
}
/**
* Create a mysql DSN string.
*
* @param string $host Host.
* @param string $dbname Database name.
* @param string $port [optional] Port.
* @param array $charset [optional] Character set.
* @return string DSN string.
*/
function getDsn($host, $dbname, $port = '3306', $charset = 'utf8') {
$dsn = sprintf('mysql:host=%s;port=%s;dbname=%s;charset=%s'
, $host
, $port
, $dbname
, $charset
);
return $dsn;
}
/**
* Close a db connection.
*
* @param PDO $connection Db connection.
* @return void
*/
function closeConnection($connection) {
$connection = NULL;
}
/**
* Get the data type of a binding value.
*
* @param mixed $value Binding value.
* @return mixed Data type of the binding value.
*/
function getInputParameterDataType($value) {
$dataType = PDO::PARAM_STR;
if (is_int($value)) {
$dataType = PDO::PARAM_INT;
} elseif (is_bool($value)) {
$dataType = PDO::PARAM_BOOL;
}
return $dataType;
}
/*
* ---------------
* Print functions
* ---------------
*/
/**
* Print data on screen.
*
* @param mixed $data Data to print.
* @param bool $preformatted Print preformatted if TRUE, print normal otherwise.
* @return void
*/
function printData($data, $preformatted = FALSE) {
if ($preformatted) {
echo '<pre>' . print_r($data, true) . '</pre>';
} else {
echo $data;
}
}
/*
* -------------------------
* Error reporting functions
* -------------------------
*/
/**
* Toggle error reporting.
*
* @param integer $level Error level.
* @param bool $display_errors Display errors if TRUE, hide them otherwise.
* @return void
*/
function activateErrorReporting($level = E_ALL, $display_errors = TRUE) {
error_reporting($level);
ini_set('display_errors', ($display_errors ? 1 : 0));
}
EDIT 1: Additional measure against MySQL injection:
Apply qoute() like this:
function getCardsInRadius($connection, ...) {
$longitude = $connection->quote($longitude);
$latitude = $connection->quote($latitude);
//...
}
EDIT 2: Resolve bad approximations:
In your original code you have used cos(radians($latitude))*69:
...
between ($longitude-$miles/cos(radians($latitude))*69)
and ($longitude+$miles/cos(radians($latitude))*69)
...
In my code I used abs(cos(deg2rad($latitude)) * 69). As I recall I choosed this deliberately:
$rectLong1 = $longitude - $radius / abs(cos(deg2rad($latitude)) * 69);
$rectLong2 = $longitude + $radius / abs(cos(deg2rad($latitude)) * 69);
It seems that this could be the problem. So, first, replace deg2rad with radians. It should then be:
$rectLong1 = $longitude - $radius / abs(cos(radians($latitude)) * 69);
$rectLong2 = $longitude + $radius / abs(cos(radians($latitude)) * 69);
If it still doesn't work, then remove abs. It should then be:
$rectLong1 = $longitude - $radius / (cos(radians($latitude)) * 69);
$rectLong2 = $longitude + $radius / (cos(radians($latitude)) * 69);
Be aware of the parenthesis positions.
EDIT 3 - Variable measurement unit:
Apply a variable measurement unit (km, miles, etc) as function parameter and change the earth radius correspondingly. How you inject the $measurementUnit into the function is your part. Yes, the earth radius is almost 3959 miles.
function getCardsInRadius($connection, $word, $longitude, $latitude, $radius, $limit = 100, $measurementUnit = 'miles') {
//...
switch ($measurementUnit) {
case 'miles':
$earthRadius = 3959;
break;
case 'km':
$earthRadius = 6371;
break;
default: // miles
$earthRadius = 3959;
break;
}
$distance = sprintf('%s * 2 * ...'
, $earthRadius
, $latitude
, $latitude
, $longitude
);
//...
}
