Bazaar – php example code – part 10 – pagination in list output
Article will focus on way how to create pagination functionality for larger list of displayed data (list of items for sell, list of registered users, cart items, …). Our implementation will be portable among a pages or other projects, visually attractive and ergonomic.
Expectation from pagination functionality
Larger list output of data can be for user a bit overwhelming. Best way how to break output into a separate chunks of data is paginate them with support of separate numbered page links.
For our implementation of pagination mechanism is expected:
user can define or in code is hardcoded number of items per page, this is held in $number_per_page variable
page links must be shown as numbered hyperlinks in bottom left part of data lists
arrows for next and previous page must be present with its graphical representation
separate function for generating pagination link will be created (its name is functions.php and appropriate function is named function generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) { //($user_search, $sort_by, $order, $cur_page, $num_pages);
Our expectation how pagination links will look like show next picture:
Code for page links generation
Function for paging links creation is inserted into a functions.php script. Full content of mentioned function follows:
// Loop through the pages generating the page numbered links
for($i = 1; $i <= $num_pages; $i++) {
if ($cur_page == $i) {
$page_links .= ‚<span id=“pagination“>‘ . $i. ‚</span>‘; // span inline element mark non a tag (unlinked number) as pagination for further formating by css
Our function can generate also user_search if needed for providing optional chunk of information trough GET able links.
In main implementation page we take closer look in a way how to display paginated outputs.
Way how to implement pagination in a main page code
For first approach we will show you code implementing pagination with calling generate_page_links($user_search, $sort_by, $order, $cur_page, $num_pages) function.
As a comments are displayed necessary leadings for understanding our code. Next code sniped is selected from main Bazaar page, from bottom list of items for sell (permanently displayed part, not part generated by user selected category – upper part is optionally displayed).
// results per page default declater as 5 on top of page and changed in submitt part after reset button handling $results_per_page = 5;
$skip = (($cur_page -1) * $results_per_page);
// first question to database table for obtaining number of published items in a database – obtain value for $total
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC „; // read in reverse order of score – highest score first
$total = mysqli_num_rows($output_for_number_rows_count); //get number of rows in databse
… omitted part of code enabling ordering along selected category
if(($sort_by == „name“) && ($order == „1“)) { // along name and ASC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item ASC LIMIT$skip, $results_per_page“;
};
if(($sort_by == „name“) && ($order == „-1“)) { // along name and DESC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY name_of_item DESC LIMIT $skip, $results_per_page“;
};
if(($sort_by == „price“) && ($order == „1“)) { // along price and ASC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur ASC LIMIT $skip, $results_per_page“;
};
if(($sort_by == „price“) && ($order == „-1“)) { // along price and DESC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY price_eur DESC LIMIT $skip, $results_per_page“;
};
if(($sort_by == „category“) && ($order == „1“)) { // along category and ASC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id ASC LIMIT $skip, $results_per_page“;
};
if(($sort_by == „category“) && ($order == „-1“)) { // along category and DESC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY subcategory_id DESC LIMIT $skip, $results_per_page“;
};
if(($sort_by == „default“)) { // along category and DESC order
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“;
};
} else { // first run without ordering – no get link generated
$sql = „SELECT * FROM bazaar_item WHERE published=“.“‚1′“.“ AND cart_number=“.“‚0′“.“ ORDER BY item_id DESC LIMIT $skip, $results_per_page“; // read in reverse order of score – highest score first
<label> Set expected number of items per page -5 is default:</label>
<input list=“number_per_page“ name=“number_per_page“ placeholder=“please select or write nr.“>
<datalist id=“number_per_page“> <!– must be converted in subcategory_id in script – marked with (*) –>
<option value=“5″>
<option value=“10″>
<option value=“15″>
<option value=“20″>
<option value=“50″>
<option value=“100″>
</datalist>
<!– users_id from session obtaining – for debuging and testing is set as hidden –>
<button type=“submit“ name=“nr_of_pages“ class=“btn btn-warning“> Use selected number of pages! </button>
</div>
</form>
<?php
echo „<br>“; echo „<br>“;
if($output = mysqli_query($dbc, $sql)){
if(mysqli_num_rows($output) > 0){ // if any record obtained from SELECT query
// create table output
echo „<table>“; //head of table
echo „<tr>“;
//echo „<th>id</th>“;
// functionality for ordering result
/**
* SORTING – PART I. Here are generated GET links for UP/DOWN ordering by appropriate category – not pertinent to our explantation related to pagination functionality
echo „There is no item for sell. Please add one.“; // if no records in table
}
} else{
echo „ERROR: Could not able to execute $sql. “ . mysqli_error($dbc); // if database query problem
}
// Close connection
mysqli_close($dbc);
?>
By this way we can implement pagination also in admin page. If two or more ordering are present on page, only one selected number of page is mandatory for all lists. it means, if we selected page nr. 2 in one list, also other list are on page 2 after reloading page with appropriate GET links is generated for requesting that page for display.
This is not bad solution of most of our needs for simplifying information displayed in one time.
Conclusion
Pagination is a way how to limit number off information visible to a user in given time. For our solution we used selection with LIMIT functionality and further mechanism for reading total number of items for display. After necessary calculation, links for pagination are generated and with help of GET request, page obtain all information important for display only that part of selection, that is needed for that moment.
Full working code of bazaar training app can be downloaded from github here.
Main parts are implemented in as function in functions.php where is function for link generation. Index.php or admin.php are great examples how to implement two or more pagination per single page. This simple solution has its own limitation as it was mentioned before.