StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
primarykey
Id
16827352
data
AcceptedAnswerId
0
AnswerCount
0
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-05-30T03:13:32.863
FavoriteCount
0
LastActivityDate
2013-05-30T03:13:32.863
LastEditDate
LastEditorUserId
0
OwnerUserId
2431311
ParentId
16821972
PostTypeId
2
Score
0
ViewCount
0
LastEditorDisplayName
text
Body
As David Graham says, you're missing your LIMIT and OFFSET. The CodeIgniter pagination is fairly basic, since the underlying "ActiveRecord" is itself very basic (in essence, a query builder, NOT an ORM). So, you need to think on the SQL level, how do you get "pages" of results that you want. Below I am including sample code from one of my projects. CONTROLLER: <pre><code> // set up pagination $itemsPerPage = 20; $totalItems = $this->Location_model->count_locations( $locationName, $locationType, $locationParent ); // get itemStartIndex from URL if specified, otherwise default to 0 if ( $this->uri->segment(3) ) { $itemStartIndex = $this->uri->segment(3); } else { $itemStartIndex = '0'; } $this->load->library('pagination'); $config['base_url'] = site_url('admin/locations'); $config['total_rows'] = $totalItems; $config['per_page'] = $itemsPerPage; $config['uri_segment'] = 3; // store offset in case user triggers a function and we want to come back to same page $this->session->set_flashdata('pagination_offset', $itemStartIndex); $this->pagination->initialize($config); $data['pagination'] = $this->pagination->create_links(); // get current locations from database $records = $this->Location_model->get_locations( $locationName, $locationType, $locationParent, $itemStartIndex, $itemsPerPage ); ... // now $records gets passed to view for display </code></pre> MODEL: <pre><code>function count_locations($locationName = '', $locationType = '', $locationParent = '') { $this->db->select('id'); if ( $locationName != '' ) { $this->db->like('name', $locationName); } if ( $locationType != '') { $this->db->where('location_type', $locationType); } if ( $locationParent != '') { $this->db->where('parent_id', $locationParent); } $query = $this->db->get('locations'); return $query->num_rows(); } function get_locations($locationName = '', $locationType = '', $locationParent = '', $startIndex = '0', $limit = '') { $this->db->select('*'); if ( $locationName != '' ) { $this->db->like('name', $locationName); } if ( $locationType != '') { $this->db->where('location_type', $locationType); } if ( $locationParent != '') { $this->db->where('parent_id', $locationParent); } $this->db->order_by('name', 'asc'); $query = $this->db->get('locations', $limit, $startIndex); if ($query->num_rows() > 0) { return $query; } else { return FALSE; } } </code></pre> Notice that my method to do the query has the optional parameters $startIndex and $limit. These are MANDATORY for paging to work. The URI segment (in my case, segment 3) is going to directly tell your controller what OFFSET to use, and the <code>per_page</code> that you specify translates to the LIMIT (which in my case I use $itemsPerPage to store since it's referenced multiple times - actually I use a global value in my case but I hard coded it here for easier reference). Note you will also need a count method. You COULD just use your same query, load ALL results, and count it in the controller, but I like to use a separate count method that only generates a minimal query using my WHERE restrictions and selecting just COUNT('id'), which speeds it up considerably. (the downside of course is it's an anti-pattern to DRY since the same query code must be defined twice, once for your count method and once for the data querying method) NOTE ON YOUR CODE Instead of specifying those or_where statements in the controller, I would put them in the model, under a method that describes your search. This sort of search logic should definitely be in the model - the controller should only be accepting the routing, passing the inputs to the model, and then setting up the pagination and view with the results.
Tags
Title
singulars
PostAcceptedAnswerId
1. This table or related slice is empty.
PostParentId
1. POHow to implement search and pagination in CodeIgniter 2.1's database API?
 singulars
 PostTypePostTypeId
 PTQuestion
PostTypePostTypeId
1. PTAnswer
UserLastEditorUserId
1. This table or related slice is empty.
UserOwnerUserId
1. USequalizer
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. This table or related slice is empty.
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.