Reputation: 417
I really can't see where the issue is here.
EX: A BMW is on sale in New York (city_id = 5) and Connecticut (city_id = 3), so when user enters BMW in search box it returns 2 results where it should only be 1.
public function search_result_count($search) {
$count=0;
$search = addslashes($search);
$city_id = $this->tank_auth->get_user_city()->id;
$sql="select count(id) as count from ".$this->table_name."
where title LIKE '%$search%' or zipcode like '%$search%'
and city_id ='$city_id' ";
$query=$this->db->query($sql);
if($row=$query->row()) {
$count=$row->count;
}
return $count;
}
Even if I assign $city_id = "5" still the same thing, what would be the problem here?
Upvotes: 0
Views: 91
Reputation: 1796
try in CI format
$this->db->select('count(id) as count');
$this->db->from('$this->table_name');
$this->db->where('city_id', $city_id);
$this->db->where("title LIKE '%".$search."%' or zipcode like '%".$search."%'");
$query = $this->db->get();
if($query->num_rows() > 0){
return $query->result_array();
}
}
Upvotes: 1
Reputation: 18601
Try with GROUP BY you miss that
$sql="select count(id) as count from ".$this->table_name."
where ((title LIKE '%$search%' or zipcode like '%$search%')
and city_id ='$city_id') GROUP BY $BMW_COL ";
Group by BMW field name
Upvotes: 1
Reputation: 306
the (city_id ='$city_id')
condtion will be skipped in the first section of OR
statement. try like this
$sql="select count(id) as count from ".$this->table_name."
where ((title LIKE '%$search%') and (city_id ='$city_id'))
or
((zipcode like '%$search%') and (city_id ='$city_id')) ";
Upvotes: 1
Reputation: 4753
select count(id) as count from ".$this->table_name."
where (title LIKE '%$search%' or zipcode like '%$search%')
and (city_id ='$city_id)
Upvotes: 0