Search Coffeeshops

= '$search_total_rating' AND address LIKE '%$search_shop_address%' AND shop_name LIKE '%$search_shop_name%' AND services_vapo LIKE '%$vapo%' AND services_pool LIKE '%$pool%' AND services_snacks LIKE '%$snacks%' AND services_disabled LIKE '%$daccess%' AND services_tv LIKE '%$tv%' AND services_games LIKE '%$games%' AND services_cat LIKE '%$cat%' AND services_internet LIKE '%$internet%' AND services_spacecake LIKE '%$spacecake%' AND services_bud LIKE '%$bud%' AND services_booze LIKE '%$booze%' AND comfort >= '$search_shop_comfort' AND service >= '$search_shop_service' AND ambience >= '$search_shop_ambience' AND friendliness >= '$search_shop_friendliness' AND spacious >= '$search_shop_spacious' AND experience >= '$search_shop_experience' AND bud_quality >= '$search_shop_bud_quality' AND bud_price >= '$search_shop_bud_price' AND drink_price >= '$search_shop_drink_price' AND space_cake >= '$search_shop_space_cake' AND accessibility >= '$search_shop_accessibility' AND toilet_access >= '$search_shop_toilet_access' AND (shop_open < shop_close AND '".$nltime."' BETWEEN shop_open AND shop_close) OR (shop_open > shop_close AND ('".$nltime."' >= shop_open OR '".$nltime."' < shop_close)) ORDER BY shop_name ASC"); } else { $result = mysql_query("SELECT coffeeshops.*, services.*, final_total FROM coffeeshops inner join services on coffeeshops.shop_id=services.shop_id inner join ( select avg(vote) AS final_total, shop_id FROM votes GROUP BY shop_id) as temp on coffeeshops.shop_id=temp.shop_id WHERE temp.final_total > ".$search_total_rating_min." AND temp.final_total < ".$search_total_rating_max." AND shop_name LIKE '%$search_shop_name%' AND services_vapo LIKE '%$vapo%' AND services_pool LIKE '%$pool%' AND services_snacks LIKE '%$snacks%' AND services_disabled LIKE '%$daccess%' AND services_tv LIKE '%$tv%' AND services_games LIKE '%$games%' AND services_cat LIKE '%$cat%' AND services_internet LIKE '%$internet%' AND services_spacecake LIKE '%$spacecake%' AND services_bud LIKE '%$bud%' AND services_booze LIKE '%$booze%' GROUP BY shop_name ORDER BY shop_name ASC"); } //$sql = "SELECT distinct coffeeshops.*, services.*, ratings.*, temp.total as final_total, //CONCAT(shop_number, ' ', shop_location, ' ', shop_city, ' ', shop_province, ' ', shop_postcode) AS address FROM coffeeshops //inner join services on coffeeshops.shop_id=services.shop_id ////inner join ratings on coffeeshops.shop_id=ratings.shop_id //inner join //(select SUM(comfort + service + ambience + friendliness + spacious + experience + bud_quality + bud_price + drink_price + space_cake + accessibility + toilet_access)/(12) / COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id ) as temp on coffeeshops.shop_id=temp.shop_id //HAVING temp.total >= '$search_total_rating' //////////////////////////////////// Adam's Pagination Logic /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// $nr = mysql_num_rows($result); // Get total of Num rows from the database query if (isset($_GET['pn'])) { // Get pn from URL vars if it is present $pn = preg_replace('#[^0-9]#i', '', $_GET['pn']); // filter everything but numbers for security(new) //$pn = ereg_replace("[^0-9]", "", $_GET['pn']); // filter everything but numbers for security(deprecated) } else { // If the pn URL variable is not present force it to be value of page number 1 $pn = 1; } $paginationNumber = ""; if(strlen($search_shop_name) > 0) { $paginationNumber .= '&search_shop_name=' . $search_shop_name; } if(strlen($search_shop_address) > 0) { $paginationNumber .= '&search_shop_address=' . $search_shop_address; } if($search_open_now == "on") { $paginationNumber .= '&search_open_now=' . $search_open_now; } if($search_total_rating > 0) { $paginationNumber .= '&search_total_rating=' . $search_total_rating; } if($vapo == "yes") { $paginationNumber .= '&vapo=' . $vapo; } if($pool == "yes") { $paginationNumber .= '&pool=' . $pool; } if($snacks == "yes") { $paginationNumber .= '&snacks=' . $snacks; } if($daccess == "yes") { $paginationNumber .= '&daccess=' . $daccess; } if($tv == "yes") { $paginationNumber .= '&tv=' . $tv; } if($games == "yes") { $paginationNumber .= '&games=' . $games; } if($cat == "yes") { $paginationNumber .= '&cat=' . $cat; } if($internet == "yes") { $paginationNumber .= '&internet=' . $internet; } if($spacecake == "yes") { $paginationNumber .= '&spacecake=' . $spacecake; } if($bud == "yes") { $paginationNumber .= '&bud=' . $bud; } if($booze == "yes") { $paginationNumber .= '&booze=' . $booze; } if($search_shop_comfort > 0) { $paginationNumber .= '&search_shop_comfort=' . $search_shop_comfort; } if($search_shop_service > 0) { $paginationNumber .= '&search_shop_service=' . $search_shop_service; } if($search_shop_ambience > 0) { $paginationNumber .= '&search_shop_ambience=' . $search_shop_ambience; } if($search_shop_friendliness > 0) { $paginationNumber .= '&search_shop_friendliness=' . $search_shop_friendliness; } if($search_shop_spacious > 0) { $paginationNumber .= '&search_shop_spacious=' . $search_shop_spacious; } if($search_shop_experience > 0) { $paginationNumber .= '&search_shop_experience=' . $search_shop_experience; } if($search_shop_bud_quality > 0) { $paginationNumber .= '&search_shop_bud_quality=' . $search_shop_bud_quality; } if($search_shop_bud_price > 0) { $paginationNumber .= '&search_shop_bud_price=' . $search_shop_bud_price; } if($search_shop_drink_price > 0) { $paginationNumber .= '&search_shop_drink_price=' . $search_shop_drink_price; } if($search_shop_space_cake > 0) { $paginationNumber .= '&search_shop_space_cake=' . $search_shop_space_cake; } if($search_shop_accessibility > 0) { $paginationNumber .= '&search_shop_accessibility=' . $search_shop_accessibility; } if($search_shop_toilet_access > 0) { $paginationNumber .= '&search_shop_toilet_access=' . $search_shop_toilet_access; } //This is where we set how many database items to show on each page $itemsPerPage = 10; // Get the value of the last page in the pagination result set $lastPage = ceil($nr / $itemsPerPage); // Be sure URL variable $pn(page number) is no lower than page 1 and no higher than $lastpage if ($pn < 1) { // If it is less than 1 $pn = 1; // force if to be 1 } else if ($pn > $lastPage) { // if it is greater than $lastpage $pn = $lastPage; // force it to be $lastpage's value } // This creates the numbers to click in between the next and back buttons $centerPages = ""; // Initialize this variable $sub1 = $pn - 1; $sub2 = $pn - 2; $add1 = $pn + 1; $add2 = $pn + 2; // Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax // $sql2 is what we will use to fuel our while loop statement below switch ($sort_by) { case 'shop_name': case 'shop_id': case 'final_total': break; default: $sort_by = 'shop_name'; } // get the direction, or use the default $direction = isset($_GET['d']) ? $_GET['d'] : 'ASC'; if ($direction != 'ASC' && $direction != 'DESC') { $direction = 'ASC'; } $paginationDisplay = ""; // Initialize the pagination output variable // This creates the numbers to click in between the next and back buttons if ($pn == 1) { $centerPages .= ' ' . $pn . ' '; $centerPages .= ' ' . $add1 . ' '; } else if ($pn == $lastPage) { $centerPages .= ' ' . $sub1 . ' '; $centerPages .= ' ' . $pn . ' '; } else if ($pn > 2 && $pn < ($lastPage - 1)) { $centerPages .= ' ' . $sub2 . ' '; $centerPages .= ' ' . $sub1 . ' '; $centerPages .= ' ' . $pn . ' '; $centerPages .= ' ' . $add1 . ' '; $centerPages .= ' ' . $add2 . ' '; } else if ($pn > 1 && $pn < $lastPage) { $centerPages .= ' ' . $sub1 . ' '; $centerPages .= ' ' . $pn . ' '; $centerPages .= ' ' . $add1 . ' '; } // This line sets the "LIMIT" range... the 2 values we place to choose a range of rows from database in our query $limit = 'LIMIT ' .($pn - 1) * $itemsPerPage .',' .$itemsPerPage; // Now we are going to run the same query as above but this time add $limit onto the end of the SQL syntax // This code runs only if the last page variable is not equal to 1, if it is only 1 page we require no paginated links to display if ($lastPage != "1"){ // This shows the user what page they are on, and the total number of pages $paginationDisplay .= 'Page ' . $pn . ' of ' . $lastPage. '

' : '

'); // used to build urls to reverse the current sort direction $reverse_direction = ($direction == 'DESC' ? 'ASC' : 'DESC'); // build the basis for the query if($search_open_now == "on") { $sql="SELECT coffeeshops.*, services.*, ratings.*, temp.total as final_total, CONCAT(shop_number, ' ', shop_location, ' ', shop_city, ' ', shop_province, ' ', shop_postcode) AS address FROM coffeeshops inner join services on coffeeshops.shop_id=services.shop_id inner join ratings on coffeeshops.shop_id=ratings.shop_id inner join (select SUM(comfort + service + ambience + friendliness + spacious + experience + bud_quality + bud_price + drink_price + space_cake + accessibility + toilet_access)/(12) / COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id ) as temp on coffeeshops.shop_id=temp.shop_id WHERE (shop_open < shop_close AND '".$nltime."' BETWEEN shop_open AND shop_close) OR (shop_open > shop_close AND ('".$nltime."' >= shop_open OR '".$nltime."' < shop_close)) HAVING temp.total >= '$search_total_rating' AND address LIKE '%$search_shop_address%' AND shop_name LIKE '%$search_shop_name%' AND services_vapo LIKE '%$vapo%' AND services_pool LIKE '%$pool%' AND services_snacks LIKE '%$snacks%' AND services_disabled LIKE '%$daccess%' AND services_tv LIKE '%$tv%' AND services_games LIKE '%$games%' AND services_cat LIKE '%$cat%' AND services_internet LIKE '%$internet%' AND services_spacecake LIKE '%$spacecake%' AND services_bud LIKE '%$bud%' AND services_booze LIKE '%$booze%' AND comfort >= '$search_shop_comfort' AND service >= '$search_shop_service' AND ambience >= '$search_shop_ambience' AND friendliness >= '$search_shop_friendliness' AND spacious >= '$search_shop_spacious' AND experience >= '$search_shop_experience' AND bud_quality >= '$search_shop_bud_quality' AND bud_price >= '$search_shop_bud_price' AND drink_price >= '$search_shop_drink_price' AND space_cake >= '$search_shop_space_cake' AND accessibility >= '$search_shop_accessibility' AND toilet_access >= '$search_shop_toilet_access' ORDER BY shop_name ASC"; } else { $on_shopname = "AND shop_name LIKE '%$search_shop_name%'"; $sql = "SELECT coffeeshops.*, services.*, final_total FROM coffeeshops inner join services on coffeeshops.shop_id=services.shop_id inner join (select avg(vote) AS final_total, shop_id FROM votes GROUP BY shop_id) as temp on coffeeshops.shop_id=temp.shop_id WHERE final_total >= '$search_total_rating' AND shop_name LIKE '%$search_shop_name%' AND services_vapo LIKE '%$vapo%' AND services_pool LIKE '%$pool%' AND services_snacks LIKE '%$snacks%' AND services_disabled LIKE '%$daccess%' AND services_tv LIKE '%$tv%' AND services_games LIKE '%$games%' AND services_cat LIKE '%$cat%' AND services_internet LIKE '%$internet%' AND services_spacecake LIKE '%$spacecake%' AND services_bud LIKE '%$bud%' AND services_booze LIKE '%$booze%' GROUP BY shop_name ORDER BY $sort_by $direction ,shop_name ASC $limit"; //$sql = "SELECT distinct coffeeshops.*, services.*, ratings.*, temp.total as final_total, //CONCAT(shop_number, ' ', shop_location, ' ', shop_city, ' ', shop_province, ' ', shop_postcode) AS address FROM coffeeshops //inner join services on coffeeshops.shop_id=services.shop_id ////inner join ratings on coffeeshops.shop_id=ratings.shop_id //inner join //(select SUM(comfort + service + ambience + friendliness + spacious + experience + bud_quality + bud_price + drink_price + space_cake + accessibility + toilet_access)/(12) / COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id ) as temp on coffeeshops.shop_id=temp.shop_id //HAVING temp.total >= '$search_total_rating' //AND address LIKE '%$search_shop_address%' AND shop_name LIKE '%$search_shop_name%' //AND toilet_access >= '$search_shop_toilet_access' //ORDER BY $sort_by $direction $limit"; } // execute query, get results $res = mysql_query($sql); $results = array(); if ($res) { while ($row = mysql_fetch_assoc($res)) { $results[] = $row; } //print_r($results); } // print $sql; $numrows = mysql_num_rows($result); if ($numrows == 0) { echo "

No matches found, try again!

"; // exit; } echo $numrows; ///////////////////////////////////// END Adam's Pagination Display Setup ///////////////////////////////////////////////////// // Build the Output Section Here ?>

" method="GET">

Search by Criteria

Coffeeshop:
Address:
Total Rating:
Open Now: />

Search by Ratings

Comfort:
Service:
Ambience:
Friendliness:
Spacious:
Experience:
Bud quality:
Bud price:
Drink Price:
Space cake:
Accessibility:
Toilet access:

0) { ?>

Found Coffeeshops

10) { ?>

0) { $paginationSort .= '&search_shop_name=' . $search_shop_name; } if(strlen($search_shop_address) > 0) { $paginationSort .= '&search_shop_address=' . $search_shop_address; } if($search_open_now == "on") { $paginationSort .= '&search_open_now=' . $search_open_now; } if($search_total_rating > 0) { $paginationSort .= '&search_total_rating=' . $search_total_rating; } if($vapo == "yes") { $paginationSort .= '&vapo=' . $vapo; } if($pool == "yes") { $paginationSort .= '&pool=' . $pool; } if($snacks == "yes") { $paginationSort .= '&snacks=' . $snacks; } if($daccess == "yes") { $paginationSort .= '&daccess=' . $daccess; } if($tv == "yes") { $paginationSort .= '&tv=' . $tv; } if($games == "yes") { $paginationSort .= '&games=' . $games; } if($cat == "yes") { $paginationSort .= '&cat=' . $cat; } if($internet == "yes") { $paginationSort .= '&internet=' . $internet; } if($spacecake == "yes") { $paginationSort .= '&spacecake=' . $spacecake; } if($bud == "yes") { $paginationSort .= '&bud=' . $bud; } if($booze == "yes") { $paginationSort .= '&booze=' . $booze; } if($search_shop_comfort > 0) { $paginationSort .= '&search_shop_comfort=' . $search_shop_comfort; } if($search_shop_service > 0) { $paginationSort .= '&search_shop_service=' . $search_shop_service; } if($search_shop_ambience > 0) { $paginationSort .= '&search_shop_ambience=' . $search_shop_ambience; } if($search_shop_friendliness > 0) { $paginationSort .= '&search_shop_friendliness=' . $search_shop_friendliness; } if($search_shop_spacious > 0) { $paginationSort .= '&search_shop_spacious=' . $search_shop_spacious; } if($search_shop_experience > 0) { $paginationSort .= '&search_shop_experience=' . $search_shop_experience; } if($search_shop_bud_quality > 0) { $paginationSort .= '&search_shop_bud_quality=' . $search_shop_bud_quality; } if($search_shop_bud_price > 0) { $paginationSort .= '&search_shop_bud_price=' . $search_shop_bud_price; } if($search_shop_drink_price > 0) { $paginationSort .= '&search_shop_drink_price=' . $search_shop_drink_price; } if($search_shop_space_cake > 0) { $paginationSort .= '&search_shop_space_cake=' . $search_shop_space_cake; } if($search_shop_accessibility > 0) { $paginationSort .= '&search_shop_accessibility=' . $search_shop_accessibility; } if($search_shop_toilet_access > 0) { $paginationSort .= '&search_shop_toilet_access=' . $search_shop_toilet_access; } ?> Title Rating Total