Get correct data from Codeigniter query?

Question

I'm currently trying to get an array from my database using codeigniter active record which something I do often but I'm currently having a problem getting the correct data.

There is 4 records which should be getting returned to my controller from my model. At the moment all records from the table are being returned.

I'm using a foreach loop within my query as the queries where/or_where clause can be many values. This is where the problem lies as I think it bypasses the foreach and get's all records without looking at the where clause.

My question is regarding what seems to be the problem with the 2nd example?

WORKING

The following code gets the desired 4 records which should be returned:

public function get_most_relevant($user){

    $categories = $this->get_users_most_common_categories($user);

    $this->db->order_by('posts.date_created','DESC');
    $this->db->limit(25);
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->select('posts.image_name as post_image');
    $this->db->select('posts.random_string as post_string');
    $this->db->select('posts.date_created as post_creation');
    $this->db->join('users', 'posts.user_string = users.random_string','left'); 
    $this->db->where('category_string', 'cCU8oEQHYLWP');
    $this->db->or_where('category_string', '8RfRDWrG5QB7');

    $posts = $this->db->get();

    return $posts;

}

NOT WORKING

The following code gets all records from the table:

It appears to be bypassing the foreach and going straight to the get().

public function get_most_relevant($user){

    $categories = $this->get_users_most_common_categories($user);
    $i = 0;

    $this->db->order_by('posts.date_created','DESC');
    $this->db->limit(25);
    $this->db->select('*');
    $this->db->from('posts');
    $this->db->select('posts.image_name as post_image');
    $this->db->select('posts.random_string as post_string');
    $this->db->select('posts.date_created as post_creation');
    $this->db->join('users', 'posts.user_string = users.random_string','left'); 

    foreach($categories->result_array() as $category){
        if($i == 0){
            $this->db->where( array('category_string' => $category['category_string'], 'published' => '1') );
        }else{
            $this->db->or_where( array('category_string' => $category['category_string'], 'published' => '1') );
        }   
        $i++;
    }

    $posts = $this->db->get();

    return $posts;

}

$this->get_users_most_common_categories($user) code

public function get_users_most_common_categories($user){

    $categories = $this->db->query("SELECT category_string, COUNT(category_string) AS category_occurence FROM views WHERE user_string = '".$user."' OR ip_address = '".$user."' GROUP BY category_string ORDER BY category_occurence DESC");

    return $categories;

}

$this->get_users_most_common_categories($user) return

Array
(
[0] => Array
    (
        [category_string] => cCU8oEQHYLWP
        [category_occurence] => 3
    )

[1] => Array
    (
        [category_string] => 8RfRDWrG5QB7
        [category_occurence] => 1
    )

)

QUERY OUTPUT

SELECT *, posts.image_name as post_image, posts.random_string as post_string, posts.date_created as post_creation FROM (posts) LEFT JOIN users ON posts.user_string = users.random_string WHERE category_string = 'cCU8oEQHYLWP' AND published = '1' OR category_string = '8RfRDWrG5QB7' OR published = '1' ORDER BY posts.date_created DESC LIMIT 25

Thanks for your help in advance, it is much appreciated.

Answer 1

It may be a bug. You could try writing that part of the query as a string which you can pass to where and or_where:

foreach($categories->result_array() as $category){
    $query = "(category_string = ".$category['category_string']." AND published = '1')";

    if($i == 0){
        $this->db->where($query);
    }else{
        $this->db->or_where($query);
    }   
    $i++;
} 

http://ellislab.com/codeigniter/user-guide/database/… - see where part 4 Custom String

Answer 2

One thing you can do to find out where did you go wrong in your query is to use the last_query function of the CI DB driver. It returns the last SQL string that was run. You can then execute that query into your database client and adjust it until you get the result that you want.

$posts = $this->db->get();
$sql = $this->db->last_query();
echo $sql;