10.13.1. ProblemYou want to display a large dataset a page at a time and provide links that move through the dataset. 10.13.2. SolutionUse database-appropriate syntax to grab just a section of all the rows that match your query. Example 10-35 shows how this works with SQLite. Paging with SQLite
The pc_indexed_links( ) and pc_print_link( ) functions in this recipe assist with printing paging information. Example 10-36 shows them in action. Displaying paginated results
10.13.3. Discussionpc_print_link( ) is shown in Example 10-37 and pc_indexed_links( ) in Example 10-38. pc_print_link( )
pc_indexed_links( )
To use these functions, retrieve the correct subset of the data using and then print it out. Call pc_indexed_links( ) to display the indexed links. After connecting to the database, you need to make sure $offset has an appropriate value. $offset is the beginning record in the result set that should be displayed. To start at the beginning of the result set, $offset should be 1. The variable $per_page is set to how many records to display on each page, and $total is the total number of records in the entire result set. For this example, all the zodiac records are displayed, so $total is set to the count of all the rows in the entire table. The SQL query that retrieves information in the proper order is: <?php $limitedSQL = 'SELECT * FROM zodiac ORDER BY id ' . "LIMIT $per_page OFFSET " . ($offset-1); ?> The LIMIT and OFFSET keywords are how you tell SQLite to return just a subset of all matching rows. The relevant rows are retrieved by $db->query($limitedSQL), and then information is displayed from each row. After the rows, pc_indexed_links( ) provides navigation links. The output when $offset is not set (or is 1) is shown in Figure 10-3. Paginated results with pc_indexed_links( )In Figure 10-3, "6-10," "11-12," and "Next >>" are links to the same page with adjusted $offset arguments, while "<< Prev" and "1-5" are grayed out, because what they would link to is what's currently displayed. 10.13.4. See AlsoA discussion of paging in the Solar framework at http://paul-m-jones.com/blog/?p=185 and information on different database paging syntaxes at http://troels.arvin.dk/db/rdbms/#select-limit-offset. |