[eluser]Bigil Michael[/eluser]
now iam doing an advanced search for one of my project. the query used is given below
Code:
function get_query(){
$search_title = $this->session->userdata('search_title');
$search_location = $this->session->userdata('search_location');
$veg_only = $this->session->userdata('veg_only');
$min_delivery_amount = $this->session->userdata('min_delivery_amount');
$max_delivery_amount = $this->session->userdata('max_delivery_amount');
$delivery_area = $this->session->userdata('delivery_area');
$cuisine = $this->session->userdata('cuisine');
$dining_card = $this->session->userdata('dining_card');
$hdfc_card = $this->session->userdata('hdfc_card');
$chartered = $this->session->userdata('chartered');
if($search_title!=''){
$search_t = 'LOWER(\'%'.$search_title.'%\') ';
}
$query ='FROM restaurants AS r
LEFT JOIN cities AS c ON r.city = c.id
LEFT JOIN city_areas AS ca ON r.area = ca.id
LEFT JOIN (
SELECT GROUP_CONCAT( m.name ) AS menuitems, GROUP_CONCAT( m.taste ) AS menutastes, m.restaurant_id
FROM `menu` AS m '
.($search_title!='' ? 'WHERE m.name LIKE '.$search_t.' OR m.taste LIKE '.$search_t.' ' : '')
.'GROUP BY m.restaurant_id
) AS rm ON rm.restaurant_id = r.id
LEFT JOIN (
SELECT GROUP_CONCAT( sp.title ) AS offers, sp.restaurant_id
FROM `special_offers` AS sp '
.($search_title!='' ? 'WHERE sp.title LIKE '.$search_t.' ' : '')
.'GROUP BY sp.restaurant_id
) AS rsp ON rsp.restaurant_id = r.id
LEFT JOIN (
SELECT GROUP_CONCAT( cu.title ) AS cuisine, rc.restaurant_id, rc.cuisine_id
FROM `restaurant_cuisines` AS rc
LEFT JOIN cuisines AS cu ON cu.id = rc.cuisine_id '
.($search_title!='' || $cuisine!='' ? 'WHERE ':'')
.($search_title!='' ? 'cu.title LIKE '.$search_t.' ' : '')
.($search_title!='' && $cuisine!='' ? ' OR ':'')
.($cuisine!='' ? 'rc.cuisine_id IN('.$cuisine.') ' : '')
.'GROUP BY rc.restaurant_id
) AS rec ON rec.restaurant_id = r.id
';
$wheres = 'WHERE r.status=1';
$wheres1 = '';
$wheres2 = '';
$where_or_t = array();
if($search_title!=''){
$where_or_t[] = 'r.name LIKE '.$search_t;
$where_or_t[] = 'r.keywords LIKE '.$search_t;
$where_or_t[] = 'r.min_delivery_amount LIKE '.$search_t;
$where_or_t[] = 'rm.menuitems LIKE '.$search_t;
$where_or_t[] = 'rm.menutastes LIKE '.$search_t;
$where_or_t[] = 'rec.cuisine LIKE '.$search_t;
$where_or_t[] = 'rsp.offers LIKE '.$search_t;
$wheres1 = implode( ' OR ', $where_or_t );
}
$where_or_loc = array();
if($search_location!=''){
$search_loc = 'LOWER(\'%'.$search_location.'%\') ';
$where_or_loc[] = 'c.name LIKE '.$search_loc;
$where_or_loc[] = 'ca.name LIKE '.$search_loc;
$where_or_loc[] = 'r.location LIKE '.$search_loc;
$wheres2 = implode( ' OR ', $where_or_loc );
}
if(count($where_or_t)>0){
$wheres .= ' AND ( '.$wheres1.' )';
}
if(count($where_or_loc)>0){
$wheres .=' AND ( '. $wheres2. ' ) ';
}
if($this->session->userdata('delivery')=='1'){
$wheres .=' AND r.online_booking = 1 ';
}
if($this->session->userdata('booking')=='1'){
$wheres .=' AND r.table_booking = 1 ';
}
if($min_delivery_amount!='' && $max_delivery_amount!=''){
$wheres .=' AND ( (r.min_delivery_amount <> \'\') AND (r.min_delivery_amount >='.(int)$min_delivery_amount.' AND r.min_delivery_amount <= '.(int)$max_delivery_amount.') ) ';
}
if($veg
$wheres .=' AND ( veg=1 AND non_veg=0) ';
}
if($dining_card=='1'){
$wheres .=' AND (dining_card=1) ';
}
if($hdfc_card=='1'){
$wheres .=' AND (hdfc_card=1) ';
}
if($chartered=='1'){
$wheres .=' AND (chartered=1) ';
}
if($delivery_area!=''){
$wheres .=' AND r.area= \''.(int)$delivery_area.'\'';
}
$query .=$wheres;
$query .=' GROUP BY r.id';
return $query;
}
in this query except this code
Code:
.($cuisine!='' ? 'rc.cuisine_id IN('.$cuisine.') ' : '')
everything works fine. I dont know what is the mistake.
can anyone solve this problem???????
