Pagination for complex query

[eluser]tim1965[/eluser]
Hi

I havent used pagination before and i am stumped on where to start. I have a search query and i am not sure how i can pull the number of rows back to populate the pagination array

Code:

$config['total_rows'] = $total;

My query in my model looks like this

Code:

$this->db->select('master_property_reference.property_id, master_property_details.country, master_property_details.region, master_property_details.property_sleeps, master_property_details.town, master_property_details.property_name, master_property_details.property_highlights, master_property_details.property_type, master_property_details.floorspace, master_property_details.no_of_bedrooms, master_property_details.no_of_bathrooms, master_pictures.thumbnail, master_charges.currency');
$this->db->from('master_property_reference');
$this->db->join('master_property_details', 'master_property_reference.property_id =master_property_details.property_id');
$this->db->join('master_pictures', 'master_property_reference.property_id = master_pictures.property_id AND master_pictures.photo_desc = "main"');
$this->db->join('master_charges', 'master_property_reference.property_id = master_charges.property_id');
$this->db->where_not_in('master_property_reference.property_id', $not_in);
$this->db->where_in('master_property_reference.property_id', $charges);
$this->db->where('ad_completed',$t);
$this->db->like('master_property_details.country',$country);
$this->db->like('master_property_details.region',$region);



     $data['num_rows'] = $this->db->count_all_results();

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

    if($query->num_rows() >0)
        {
            $row=$query->result_array();
            return $row;
    }
    }//function end

and i am calling it from my controller

Code:

$data['results']=$this->M_search_v1->advanced_search_with_dates($not_in, $charges);

Presumably i need to pull back num_rows into my controller but i am stumped if i know how.
Many thanks in advance

[eluser]tim1965[/eluser]
Apologies for bumping my own post.
Having looked at this in a bit more depth (which i should have done b4 posting). There are some posts that talk about joins. But i guess i am trying to get a working example covering code for controller, model, and view. Ideally without running the query twice, but if i need to then so be it.
Thanks in advance for anyone willing to share some code.

[eluser]jedd[/eluser]
[quote author="tim1965" date="1252678561"]
Ideally without running the query twice, but if i need to then so be it.
[/quote]

I've always assumed this was a requirement - so I have a smaller method right next door, in my model, that just does a count. Obviously I have one of these methods for each of the things I want to paginate.

[eluser]tim1965[/eluser]
Well if thats the only wat to do it. I really wanted to see what people use and what was out there. It seems a bit of a pain to run each query twice just to get the result count.
I was thinking about trying to do a count of the result array directly in php, but just wanted to see what people were doing.
Thanks again.

[eluser]Aken[/eluser]
No need to run the query twice. You can use PHP's count() function to count the number of elements in your array (in this case, the number of rows).

[eluser]jedd[/eluser]
I don't understand how that might work.

With pagination-friendly model methods, by definition you're chucking a LIMIT in there to match your number-of-items-to-display setting, so any count against that will always return that variable (well, except the ultimate one, something between 0 and that figure).

[eluser]Aken[/eluser]
I gotta stop posting when I'm dead tired. Yeah, you pretty much have to run it twice in the long run - once to get the count, and again with the LIMIT to pull the correct posts for the page.

[eluser]Unknown[/eluser]
In case anyone stumbles across this thread, the answer they're looking for is SQL_CALC_FOUND_ROWS.

[eluser]BrainCatcher[/eluser]
Just looked and searched around on the web.

I'm trying to use COUNT(*) in my query strings to retrieve the number of rows of the result set. I'm using it too for pagination and to prevent running queries duplicate, just to retrieve the number of rows.

But now i'm having another problem: how to retrieve the value from an array?

Code:

$some_var->COUNT(*) ;

The above syntax doesn't work offcourse. But what will be the solution?

Again i'm struggling with an array, i guess i have to look for some neat courses the next month!! mirk:

Grtz BrainCatcher

[eluser]BrainCatcher[/eluser]
I ran out of idea's, let it rest for a few hour's .

And voila, the solution was there! Even pretty simple.

$this->db->select('*, COUNT(*) as count');

Just let mysql put the result in a variable, then you can retrieve the value easier.

Grtz BrainCatcher