Pagination for complex query - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Pagination for complex query (/showthread.php?tid=22483) Pages:
1
2
|
Pagination for complex query - El Forum - 09-11-2009 [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; 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'); Code: $data['results']=$this->M_search_v1->advanced_search_with_dates($not_in, $charges); Many thanks in advance Pagination for complex query - El Forum - 09-11-2009 [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. Pagination for complex query - El Forum - 09-11-2009 [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. Pagination for complex query - El Forum - 09-11-2009 [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. Pagination for complex query - El Forum - 09-11-2009 [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). Pagination for complex query - El Forum - 09-11-2009 [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). Pagination for complex query - El Forum - 09-11-2009 [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. Pagination for complex query - El Forum - 10-22-2009 [eluser]Unknown[/eluser] In case anyone stumbles across this thread, the answer they're looking for is SQL_CALC_FOUND_ROWS. Pagination for complex query - El Forum - 11-13-2009 [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 Pagination for complex query - El Forum - 11-13-2009 [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. Quote:$this->db->select('*, COUNT(*) as count'); Just let mysql put the result in a variable, then you can retrieve the value easier. Grtz BrainCatcher |