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') {