CODEWITHSUNDEEP
phpmysqlsqlcodeigniter
Che Jug
Che Jug

Reputation: 417

My query doesn't return the right count

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

Answers (4)

Dexter
Dexter

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

Sadikhasan
Sadikhasan

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

Abhith
Abhith

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

Sai Avinash
Sai Avinash

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

Related Questions