Skip to content

Instantly share code, notes, and snippets.

@utrenkner

utrenkner/_searchresults.php Secret

Created October 30, 2018 21:34
Show Gist options
  • Save utrenkner/fbdc730224ad3844cdf25d15de42a578 to your computer and use it in GitHub Desktop.
Save utrenkner/fbdc730224ad3844cdf25d15de42a578 to your computer and use it in GitHub Desktop.
Download ZIP
Directory App 2018 - geo search
Raw
_searchresults.php
<?php
require_once(__DIR__ . '/inc/config.php');
/*--------------------------------------------------
init
--------------------------------------------------*/
$total_rows = 0;
$response = array();
$query_city_id = !empty($_GET['city_id']) ? $_GET['city_id'] : 0;
$keyword = !empty($_GET['query']) ? $_GET['query'] : '';
$page = !empty($_GET['page']) ? $_GET['page'] : 1;
// check if keyword is *
if ($keyword == '*') {
$keyword = '';
}
// check city_id
if (!is_numeric($query_city_id)) {
header("HTTP/1.0 404 Not Found");
die('Invalid city id');
}
$query_city_id = (int)$query_city_id;
// check page
if (!is_numeric($page)) {
header("HTTP/1.0 404 Not Found");
die('Invalid page');
}
$page = (int)$page;
/*--------------------------------------------------
pagination
--------------------------------------------------*/
$limit = $items_per_page;
if ($page > 1) {
$offset = ($page - 1) * $limit + 1;
} else {
$offset = 1;
}
// get page
if ($page == 1) {
$pag = '';
} else {
$pag = "- $txt_page $page";
}
/*--------------------------------------------------
keyword
--------------------------------------------------*/
$query_query = explode(' ', trim($keyword));
$new_query = '';
foreach ($query_query as $v) {
if (!empty($v)) {
$new_query .= "$v* ";
}
}
$query_query = $new_query;
// city details
$query_city_name = '';
$query_state_abbr = '';
if (!empty($query_city_id)) {
$query = "SELECT city_name, state, lng, lat FROM cities WHERE city_id = :query_city_id";
$stmt = $conn->prepare($query);
$stmt->bindValue(':query_city_id', $query_city_id);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$query_city_name = $row['city_name'];
$query_state_abbr = $row['state'];
}
/*--------------------------------------------------
Count results
--------------------------------------------------*/
if (!empty($query_city_id) && !empty($keyword)) {
$user_lat = $row['lat'];
$user_lng = $row['lng'];
$max_dist = 50; // This is the maximum distance (in miles) away from $user_lat, $user_lng in which to search
$query = "SELECT place_id , 3956 * 2 *
ASIN(SQRT( POWER(SIN((:user_lat1 - lat) * pi() / 180 / 2), 2)
+ COS(:user_lat2 * pi() / 180 ) * COS(lat * pi() / 180)
*POWER(SIN((:user_lng1 - lng) * pi() / 180 / 2), 2))) AS distance
FROM places
WHERE status = 'approved' AND paid = 1
AND MATCH(place_name, description) AGAINST(:query IN BOOLEAN MODE) AND
lng BETWEEN (:user_lng2 - $max_dist / COS(RADIANS(:user_lat3)) * 69)
AND (:user_lng3 + $max_dist / COS(RADIANS(:user_lat4)) * 69)
AND lat BETWEEN (:user_lat5 - ($max_dist / 69))
AND (:user_lat6 + ($max_dist / 69))
HAVING distance < $max_dist";
$stmt = $conn->prepare($query);
$stmt->bindValue(':user_lat1', $user_lat);
$stmt->bindValue(':user_lat2', $user_lat);
$stmt->bindValue(':user_lat3', $user_lat);
$stmt->bindValue(':user_lat4', $user_lat);
$stmt->bindValue(':user_lat5', $user_lat);
$stmt->bindValue(':user_lat6', $user_lat);
$stmt->bindValue(':user_lng1', $user_lng);
$stmt->bindValue(':user_lng2', $user_lng);
$stmt->bindValue(':user_lng3', $user_lng);
$stmt->bindValue(':query', $query_query);
$stmt->execute();
$result_array = $stmt->fetchAll(PDO::FETCH_ASSOC);
$total_rows = count($result_array);
$place_id_list = "0,";
if ($total_rows > 0) {
foreach ($result_array as $row) {
$place_id_list .= $row['place_id'] . ',';
}
$place_id_list = preg_replace('/,$/', '', $place_id_list);
}
} else if (empty($query_city_id) && !empty($keyword)) {
$query = "SELECT COUNT(*) AS total_rows
FROM places
WHERE status = 'approved' AND paid = 1
AND MATCH(place_name, description) AGAINST(:query IN BOOLEAN MODE)";
$stmt = $conn->prepare($query);
$stmt->bindValue(':query', $query_query);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];
} else if (!empty($query_city_id) && empty($keyword)) {
$user_lat = $row['lat'];
$user_lng = $row['lng'];
$max_dist = 50; // This is the maximum distance (in miles) away from $user_lat, $user_lng in which to search
$query = "SELECT place_id , 3956 * 2 *
ASIN(SQRT( POWER(SIN((:user_lat1 - lat) * pi() / 180 / 2), 2)
+ COS(:user_lat2 * pi() / 180 ) * COS(lat * pi() / 180)
*POWER(SIN((:user_lng1 - lng) * pi() / 180 / 2), 2))) AS distance
FROM places
WHERE status = 'approved' AND paid = 1
AND
lng BETWEEN (:user_lng2 - $max_dist / COS(RADIANS(:user_lat3)) * 69)
AND (:user_lng3 + $max_dist / COS(RADIANS(:user_lat4)) * 69)
AND lat BETWEEN (:user_lat5 - ($max_dist / 69))
AND (:user_lat6 + ($max_dist / 69))
HAVING distance < $max_dist";
$stmt = $conn->prepare($query);
$stmt->bindValue(':user_lat1', $user_lat);
$stmt->bindValue(':user_lat2', $user_lat);
$stmt->bindValue(':user_lat3', $user_lat);
$stmt->bindValue(':user_lat4', $user_lat);
$stmt->bindValue(':user_lat5', $user_lat);
$stmt->bindValue(':user_lat6', $user_lat);
$stmt->bindValue(':user_lng1', $user_lng);
$stmt->bindValue(':user_lng2', $user_lng);
$stmt->bindValue(':user_lng3', $user_lng);
$stmt->execute();
$result_array = $stmt->fetchAll(PDO::FETCH_ASSOC);
$total_rows = count($result_array);
$place_id_list = "0,";
if ($total_rows > 0) {
foreach ($result_array as $row) {
$place_id_list .= $row['place_id'] . ',';
}
$place_id_list = preg_replace('/,$/', '', $place_id_list);
}
} else {
$total_rows = 0;
}
$pager = new DirectoryApp\PageIterator($limit, $total_rows, $page);
$start = $pager->getStartRow();
// initialize empty city and query check
$empty_city_and_query = false;
/*--------------------------------------------------
Query
--------------------------------------------------*/
if (!empty($query_city_id) && !empty($keyword)) {
$query = "SELECT p.place_id, p.place_name, p.address, p.cross_street,
p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
c.city_name, c.slug, c.state, ph.filename, ph.dir,
rev_table.avg_rating,
MATCH(place_name, description) AGAINST(:query2 IN BOOLEAN MODE) AS relevance
FROM places p
LEFT JOIN cities c ON p.city_id = c.city_id
LEFT JOIN photos ph ON p.place_id = ph.place_id
LEFT JOIN (
SELECT *,
AVG(rev.rating) AS avg_rating
FROM reviews rev
GROUP BY place_id
) rev_table ON p.place_id = rev_table.place_id
WHERE p.place_id in ($place_id_list) AND p.status = 'approved' AND paid = 1
AND MATCH(place_name, description) AGAINST(:query IN BOOLEAN MODE)
GROUP BY p.place_id
ORDER BY relevance DESC, p.feat DESC
LIMIT :start, :limit";
$stmt = $conn->prepare($query);
$stmt->bindValue(':query', $query_query);
$stmt->bindValue(':query2', $query_query);
$stmt->bindValue(':start', $start);
$stmt->bindValue(':limit', $limit);
} else if (empty($query_city_id) && !empty($keyword)) {
$query = "SELECT p.place_id, p.place_name, p.address, p.cross_street,
p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
c.city_name, c.slug, c.state, ph.filename, ph.dir,
rev_table.avg_rating,
MATCH(place_name, description) AGAINST(:query2 IN BOOLEAN MODE) AS relevance
FROM places p
LEFT JOIN cities c ON p.city_id = c.city_id
LEFT JOIN photos ph ON p.place_id = ph.place_id
LEFT JOIN (
SELECT *,
AVG(rev.rating) AS avg_rating
FROM reviews rev
GROUP BY place_id
) rev_table ON p.place_id = rev_table.place_id
WHERE p.status = 'approved' AND paid = 1
AND MATCH(place_name, description) AGAINST(:query IN BOOLEAN MODE)
GROUP BY p.place_id
ORDER BY relevance DESC, p.feat DESC
LIMIT :start, :limit";
$stmt = $conn->prepare($query);
$stmt->bindValue(':query', $query_query);
$stmt->bindValue(':query2', $query_query);
$stmt->bindValue(':start', $start);
$stmt->bindValue(':limit', $limit);
} else if (!empty($query_city_id) && empty($keyword)) {
$query = "SELECT p.place_id, p.place_name, p.address, p.cross_street,
p.postal_code, p.phone, p.area_code, p.lat, p.lng, p.state_id, p.description,
c.city_name, c.slug, c.state, ph.filename, ph.dir,
rev_table.avg_rating
FROM places p
LEFT JOIN cities c ON p.city_id = c.city_id
LEFT JOIN photos ph ON p.place_id = ph.place_id
LEFT JOIN (
SELECT *,
AVG(rev.rating) AS avg_rating
FROM reviews rev
GROUP BY place_id
) rev_table ON p.place_id = rev_table.place_id
WHERE p.place_id in ($place_id_list) AND p.status = 'approved' AND paid = 1
ORDER BY p.feat DESC
LIMIT :start, :limit";
$stmt = $conn->prepare($query);
//$stmt->bindValue(':city_id', $query_city_id);
$stmt->bindValue(':start', $start);
$stmt->bindValue(':limit', $limit);
} else { // both $query_loc and $query_query empty
$empty_city_and_query = true;
}
// now execute query
$stmt->execute();
/*--------------------------------------------------
Create list_items array
--------------------------------------------------*/
if ($total_rows > 0) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$place_id = $row['place_id'];
$place_name = $row['place_name'];
$address = $row['address'];
$cross_street = $row['cross_street'];
$place_city_name = $row['city_name'];
$place_city_slug = $row['slug'];
$place_state_id = $row['state_id'];
$place_state_abbr = $row['state'];
$postal_code = $row['postal_code'];
$area_code = $row['area_code'];
$phone = $row['phone'];
$lat = $row['lat'];
$lng = $row['lng'];
$rating = $row['avg_rating'];
$description = $row['description'];
// short description
$description = get_snippet($description, 20);
// cat icon (just use blank img for now)
$cat_icon = $baseurl . '/imgs/blank.png';
// thumb
if (!empty($row['filename'])) {
$photo_url = $pic_baseurl . '/' . $place_thumb_folder . '/' . $row['dir'] . '/' . $row['filename'];
} else {
$photo_url = $cat_icon;
}
// clean place name
$endash = html_entity_decode('&#x2013;', ENT_COMPAT, 'UTF-8');
$place_name = str_replace($endash, "-", $place_name);
$list_items[] = array(
'place_id' => $place_id,
'place_name' => e($place_name),
'place_slug' => to_slug($place_name),
'address' => e($address),
'cross_street' => e($cross_street),
'place_city_name' => $place_city_name,
'place_city_slug' => $place_city_slug,
'place_state_abbr' => $place_state_abbr,
'postal_code' => e($postal_code),
'area_code' => e($area_code),
'phone' => e($phone),
'lat' => $lat,
'lng' => $lng,
'cat_icon' => $cat_icon,
'photo_url' => $photo_url,
'rating' => $rating,
'description' => $description
);
}
}
$stmt->closeCursor();
$location = '';
if (!empty($query_city_name) && !empty($query_state_abbr)) {
$location = "$query_city_name, $query_state_abbr";
}
/*--------------------------------------------------
Replacements
--------------------------------------------------*/
if (empty($location)) {
$txt_html_title = $txt_html_title_no_loc;
$txt_meta_desc = $txt_meta_desc_no_loc;
$txt_main_title = $txt_main_title_no_loc;
$txt_html_title = str_replace('%search_term%', e($keyword), $txt_html_title);
$txt_meta_desc = str_replace('%search_term%', e($keyword), $txt_meta_desc);
$txt_main_title = str_replace('%search_term%', e($keyword), $txt_main_title);
$txt_empty_results = str_replace('%search_term%', e($keyword), $txt_empty_results);
} else {
$txt_html_title = str_replace('%search_term%', e($keyword), $txt_html_title);
$txt_html_title = str_replace('%location%', $location, $txt_html_title);
$txt_meta_desc = str_replace('%search_term%', e($keyword), $txt_meta_desc);
$txt_meta_desc = str_replace('%location%', $location, $txt_meta_desc);
$txt_main_title = str_replace('%search_term%', e($keyword), $txt_main_title);
$txt_main_title = str_replace('%location%', $location, $txt_main_title);
$txt_empty_results = str_replace('%search_term%', e($keyword), $txt_empty_results);
}
// sanitize
$keyword = e($keyword);
// template file
require_once(__DIR__ . '/templates/tpl_searchresults.php');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment