Search database by the following columns: type, corridor, description (fulltext search), and keywords (also fulltext search).
It also needs to be able to page through the results, and have the ability to just page through the ENTIRE database as well. Here is my code right now... without a class.
Code: Select all
<?php
class mapController extends appController{
public function findAction(){
$output = '';
$Business = new Business;
$sortable = array('name', 'address', 'created');
// Build pagination
$params = $this->_action->getParams();
$showpage = isset($params['showpage']) ? $params['showpage'] : 0;
$orderby = isset($params['orderby']) && in_array($params['orderby'], $sortable) ? $params['orderby'] : 'name';
$ascdesc = isset($params['ascdesc']) && $params['ascdesc'] == 'desc' ? 'desc' : 'asc';
$perpage = $this->config->view->perpage;
$gettotal = $this->db->query('SELECT COUNT(*) AS `total` FROM `' . $Business->getTable() .'` WHERE `temporary` = 0 AND `suspended` = 0');
$total_pages = ceil($gettotal->fields['total'] / $perpage);
$start = $showpage ? ($showpage * $perpage) - $perpage : 0;
$businesses = $Business->find('`temporary` = 0 AND `suspended` = 0 ORDER BY `' . $orderby . '` ' . $ascdesc . ' LIMIT ' . $start . ',' . $perpage);
foreach($businesses as $business){
if($business->loadPoint()){
$business_data = array(
'point' => $business->point->_original,
'business' => $business->toArray()
);
}
$output[] = $business_data;
}
Zend::loadClass('Zend_Json');
$json = Zend_Json::encode($output);
echo $json;
}
public function findtypeAction(){
$output = '';
$Business = new Business;
$params = $this->_action->getParams();
$type = isset($params['type']) ? $params['type'] : 'business';
$businesses = $Business->find('`type` = "' . $type . '" AND `temporary` = 0 AND `suspended` = 0');
foreach($businesses as $business){
if($business->loadPoint()){
$business_data = array(
'point' => $business->point->_original,
'business' => $business->toArray()
);
}
$output[] = $business_data;
}
Zend::loadClass('Zend_Json');
$json = Zend_Json::encode($output);
echo $json;
}
public function findcorridorAction(){
$output = '';
$Business = new Business;
$params = $this->_action->getParams();
$corridor = isset($params['corridor']) ? $params['corridor'] : 'hwy70';
$businesses = $Business->find('`corridor` = "' . $corridor . ' AND `temporary` = 0 AND `suspended` = 0"');
foreach($businesses as $business){
if($business->loadPoint()){
$business_data = array(
'point' => $business->point->_original,
'business' => $business->toArray()
);
}
$output[] = $business_data;
}
Zend::loadClass('Zend_Json');
$json = Zend_Json::encode($output);
echo $json;
}
public function searchAction(){
$output = '';
$Business = new Business;
// Build pagination
$params = $this->_action->getParams();
$showpage = isset($params['showpage']) ? $params['showpage'] : 0;
$keywords = mysql_real_escape_string($_GET['keywords']);
$perpage = $this->config->view->perpage;
$gettotal = $this->db->query('SELECT COUNT(*) AS `total` FROM `' . $Business->getTable() .'` WHERE MATCH (keywords, description) AGAINST ("' . $keywords . '") AND `temporary` = 0 AND `suspended` = 0;');
$total_pages = ceil($gettotal->fields['total'] / $perpage);
$start = $showpage ? ($showpage * $perpage) - $perpage : 0;
$businesses = $Business->find('MATCH (keywords,description) AGAINST ("' . $keywords . '") AND `temporary` = 0 AND `suspended` = 0 LIMIT ' . $start . ',' . $perpage);
foreach($businesses as $business){
if($business->loadPoint()){
$business_data = array(
'point' => $business->point->_original,
'business' => $business->toArray()
);
}
$output[] = $business_data;
}
Zend::loadClass('Zend_Json');
$json = Zend_Json::encode($output);
echo $json;
}
}
?>Oh yea... and I would also like to lose the models in this class. Since we're only searching with the 'business' model, I'd like to just do all the sql queries manually... since they would be faster, and I do not need every column returned from the database.