outputSearchForm($options);
unset($dbc);
unset($dbcSearch);
exit;
}
//make the sql-String
//all target caches are between lat - max_lat_diff and lat + max_lat_diff
$max_lat_diff = $distance / (111.12 * $multiplier[$distance_unit]);
//all target caches are between lon - max_lon_diff and lon + max_lon_diff
//TODO: check!!!
$max_lon_diff = $distance * 180 / (abs(sin((90 - $lat) * 3.14159 / 180)) * 6378 * $multiplier[$distance_unit] * 3.14159);
$lon_rad = $lon * 3.14159 / 180;
$lat_rad = $lat * 3.14159 / 180;
$sqlstr = 'CREATE TEMPORARY TABLE result_caches ENGINE=MEMORY
SELECT
(' . getCalcDistanceSqlFormula($usr !== false, $lon, $lat, $distance, $multiplier[$distance_unit]) . ') `distance`,
`caches`.`cache_id` `cache_id`
FROM `caches` FORCE INDEX (`latitude`)
LEFT JOIN `cache_mod_cords` ON `caches`.`cache_id` = `cache_mod_cords`.`cache_id` AND `cache_mod_cords`.`user_id` = :1
WHERE IFNULL(cache_mod_cords.longitude, `caches`.`longitude`) > :2
AND IFNULL(cache_mod_cords.longitude, `caches`.`longitude`) < :3
AND IFNULL(cache_mod_cords.latitude, `caches`.`latitude`) > :4
AND IFNULL(cache_mod_cords.latitude, `caches`.`latitude`) < :5
HAVING `distance` < :6';
$dbcSearch->multiVariableQuery($sqlstr, $usr['userid'], $lon - $max_lon_diff, $lon + $max_lon_diff, $lat - $max_lat_diff, $lat + $max_lat_diff, $distance);
$sqlstr = 'ALTER TABLE result_caches ADD PRIMARY KEY ( `cache_id` )';
$dbcSearch->simpleQuery($sqlstr);
$sql_select[] = '`result_caches`.`cache_id`';
$sql_from[] = '`result_caches`, `caches`';
$sql_where[] = '`caches`.`cache_id`=`result_caches`.`cache_id`';
} elseif ($options['searchtype'] == 'bycacheid') {
$sql = 'SELECT ';
if (isset($lat_rad) && isset($lon_rad)) {
$sql .= getCalcDistanceSqlFormula($usr !== false, $lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, ';
} else {
if ($usr === false) {
$sql .= '0 distance, ';
} else {
//get the users home coords
$rs_coords = sql("SELECT `latitude`, `longitude` FROM `user` WHERE `user_id`='&1'", $usr['userid']);
$record_coords = sql_fetch_array($rs_coords);
if ($record_coords['latitude'] == NULL || $record_coords['longitude'] == NULL || ($record_coords['latitude'] == 0 || $record_coords['longitude'] == 0)) {
$sql .= '0 distance, ';
} else {
$lon_rad = $record_coords['longitude'] * 3.14159 / 180;
$lat_rad = $record_coords['latitude'] * 3.14159 / 180;
$sql .= getCalcDistanceSqlFormula(true, $record_coords['longitude'], $record_coords['latitude'], 0, 1) . ' `distance`, ';
}
mysql_free_result($rs_coords);
}
}
$sql .= '`caches`.`cache_id`, `caches`.`wp_oc`';
if ($usr === false) {
$sql .= ' FROM `caches` ';
} else {
$sql .= ' FROM `caches`
LEFT JOIN `cache_mod_cords` ON `caches`.`cache_id` = `cache_mod_cords`.`cache_id` AND `cache_mod_cords`.`user_id` = ' . $usr['userid'];
}
$sql .= ' WHERE `caches`.`cache_id` IN (' . $sqlFilter . ')';
$sortby = $options['sort'];
if (isset($lat_rad) && isset($lon_rad) && $sortby == 'bydistance') {
$sql .= ' ORDER BY distance ASC';
$query .= getCalcDistanceSqlFormula($usr !== false, $lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, ';
} else {
if ($usr === false) {
$query .= '0 distance, ';
} else {
// get the users home coords
$stmt = $dbc->multiVariableQuery("SELECT `latitude`, `longitude` FROM `user` WHERE `user_id`= :1 LIMIT 1", $usr['userid']);
$record_coords = $dbc->dbResultFetchOneRowOnly($stmt);
if ($record_coords['latitude'] == NULL || $record_coords['longitude'] == NULL || ($record_coords['latitude'] == 0 || $record_coords['longitude'] == 0)) {
$query .= '0 distance, ';
} else {
// TODO: load from the users-profile
$distance_unit = 'km';
$lon_rad = $record_coords['longitude'] * 3.14159 / 180;
$lat_rad = $record_coords['latitude'] * 3.14159 / 180;
$query .= getCalcDistanceSqlFormula($usr !== false, $record_coords['longitude'], $record_coords['latitude'], 0, $multiplier[$distance_unit]) . ' `distance`, ';
}
}
}
$query .= '`caches`.`cache_id` `cache_id`, `caches`.`status` `status`, `caches`.`type` `type`, `caches`.`size` `size`, `caches`.`user_id` `user_id`, ';
if ($usr === false) {
$query .= ' `caches`.`longitude` `longitude`, `caches`.`latitude` `latitude`, 0 as cache_mod_cords_id FROM `caches` ';
} else {
$query .= ' IFNULL(`cache_mod_cords`.`longitude`, `caches`.`longitude`) `longitude`, IFNULL(`cache_mod_cords`.`latitude`, `caches`.`latitude`) `latitude`, IFNULL(cache_mod_cords.id,0) as cache_mod_cords_id
FROM `caches`
LEFT JOIN `cache_mod_cords` ON `caches`.`cache_id` = `cache_mod_cords`.`cache_id` AND `cache_mod_cords`.`user_id` = ' . $usr['userid'];
}
$query .= ' WHERE `caches`.`cache_id` IN (' . $queryFilter . ')';
/* ,AVG(`caches`.`longitude`) AS avglongitude, AVG(`caches`.`latitude`) AS avglatitude */
$sortby = $options['sort'];
if (isset($lat_rad) && isset($lon_rad) && $sortby == 'bydistance') {
$caches_per_page = 20;
$sql = 'SELECT ';
if (isset($lat_rad) && isset($lon_rad)) {
$sql .= getCalcDistanceSqlFormula($usr !== false, $lon_rad * 180 / 3.14159, $lat_rad * 180 / 3.14159, 0, $multiplier[$distance_unit]) . ' `distance`, ';
} else {
if ($usr === false) {
$sql .= '0 distance, ';
} else {
//get the users home coords
if ($usr['latitude'] == NULL || $usr['longitude'] == NULL || ($usr['latitude'] == 0 || $usr['longitude'] == 0)) {
$sql .= '0 distance, ';
} else {
$distance_unit = 'km';
$lon_rad = $usr['longitude'] * 3.14159 / 180;
$lat_rad = $usr['latitude'] * 3.14159 / 180;
$sql .= getCalcDistanceSqlFormula($usr !== false, $usr['longitude'], $usr['latitude'], 0, $multiplier[$distance_unit]) . ' `distance`, ';
}
}
}
$sql .= '`caches`.`cache_id` `cache_id`, `caches`.`status` `status`, `caches`.`type` `type`, `caches`.`size` `size`, `caches`.`user_id` `user_id`, ';
if ($usr === false) {
$sql .= ' `caches`.`longitude` `longitude`, `caches`.`latitude` `latitude`, 0 as cache_mod_cords_id
FROM `caches` ';
} else {
$sql .= ' IFNULL(`cache_mod_cords`.`longitude`, `caches`.`longitude`) `longitude`, IFNULL(`cache_mod_cords`.`latitude`,
`caches`.`latitude`) `latitude`, IFNULL(cache_mod_cords.id,0) as cache_mod_cords_id FROM `caches`
LEFT JOIN `cache_mod_cords` ON `caches`.`cache_id` = `cache_mod_cords`.`cache_id` AND `cache_mod_cords`.`user_id` = ' . $usr['userid'];
}
$sql .= ' WHERE `caches`.`cache_id` IN (' . $sqlFilter . ')';
$sortby = $options['sort'];
if (isset($lat_rad) && isset($lon_rad) && $sortby == 'bydistance') {