Skip to content

Instantly share code, notes, and snippets.

@ethmz

ethmz/_searchresults.php

Forked from utrenkner/_searchresults.php
Created November 4, 2018 07:52
Show Gist options
  • Save ethmz/67d9973bdb9b35818118fbe6607f10eb to your computer and use it in GitHub Desktop.
Save ethmz/67d9973bdb9b35818118fbe6607f10eb to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ethmz revised this gist Nov 4, 2018. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions _searchresults.php
    View file
    Original file line number Diff line number Diff line change
    @@ -11,6 +11,9 @@
    $keyword = !empty($_GET['query']) ? $_GET['query'] : '';
    $page = !empty($_GET['page']) ? $_GET['page'] : 1;

    // fix v.1.13
    $user_query = e($keyword);

    // check if keyword is *
    if ($keyword == '*') {
    $keyword = '';
  2. @utrenkner utrenkner created this gist Oct 30, 2018.
    364 changes: 364 additions & 0 deletions _searchresults.php
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,364 @@
    <?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');