close ad
 
Important WebAssist Announcement
open ad
View Menu

Web development tutorial

Numbered Pagination Using MySQLi

Tutorial created by Jamie, Motley.co.uk

Categories: MySQLi Server Behaviors, PHP

rating
5 ratings | Sign in to rate this tutorial | View comments

A simple method using a MySQLi recordset to be able to use numbered pagination .

next sectionarrow down
arrow downStep 1 - Add the MySQLi recordset

The recordset is based on a table named 'news' in the database. You can configure to whatever table name you wish.

The code in line 1 of the recordset $maxRows_News =10 is returning 10 items from the database per page, you can change that to however many records you want to show per page.

<?php
$maxRows_News =10;
$News = new WA_MySQLi_RS("News",$DBConnection,$maxRows_News);
$News->setQuery("SELECT * FROM news");
$News->execute();

$NewsTotal = new WA_MySQLi_RS("NewsTotal",$DBConnection);
$NewsTotal->setQuery("SELECT count(1) a FROM news");
$NewsTotal->execute();
$News_Total = (int)$NewsTotal->Results[0]['a'];
$Req_Pages = ceil($News_Total/$maxRows_News);
?>

arrow downStep 2 - add the pagination code

Now just add the following code to the area of your page that you wish the pagination to appear:

<?php
						
	$totalRows_News = $News_Total;
			
	$pageNum_News = 0;
		if (isset($_GET['pageNum_News'])) {
				$pageNum_News = $_GET['pageNum_News'];
			}

				$incdone = false;
				if ($maxRows_News == 0)
			{
				$maxRows_News =1;
				$incdone = true;
			}
			
			$totalPages_News = ceil($totalRows_News/$maxRows_News)-1;						


		if($totalPages_News > 0 && !$incdone ) 
		{ 
				$elems = array();
				for ($i=0;$i<$Req_Pages;$i++)
			{
				if ($i==$pageNum_News)
			{
	
				$elems[] = "<b>".($i+1)."</b>";
			}
		else
			{
				$elems[] = "<a href=\"news.php?pageNum_News=$i\">".($i+1)."</a>";
			}
		}
	
	echo implode(" | ",$elems);
	}
 ?>

arrow downReviews and comments

Comments will be sent to the author of this tutorial and may not be answered immediately. For general help from WebAssist, please visit technical support.

Sign in to add comments
rating
5 ratings | Sign in to rate this tutorial
rating

Jamie: 9 Years, 8 Months, 2 Weeks, 5 Days, 20 Hours, 53 Minutes ago

Can you post this in the forum with a link to here? Cheers. Jamie

: 9 Years, 5 Months, 1 Week, 3 Days, 13 Hours, 40 Minutes ago

I am getting a error { Fatal error: Class 'WA_MySQLi_RS' not found in records.php on line 4 }

I am using Wampserver 2.5 With apache 2.4.9, PHP 5.5.12, MySQL 5.6.17 I know that this version will not work with MySQL but it will work with MySQLI but evidently it dos not have the WA_MySQLi_RS Class in it. can you help?

rhoyle@ablewebinc.com

rating

Andrew: 9 Years, 4 Months, 4 Weeks, 2 Days, 15 Minutes ago

Hi

I have used this one of my websites and it does the job very nicely. Thank you.

However I have a small problem which is beyond my coding knowledge.

If I use a filter on the listing (eg " ... FROM items WHERE items.furtherAttention LIKE 'Y%' ORDER BY ...") it reduces the number of records to be paginated - and this causes problems.

I'd be grateful for any assistance.

Thanks

Andrew

localidiot: 8 Years, 9 Months, 3 Weeks, 1 Day, 7 Hours, 22 Minutes ago

I dont see where the offset is set. How are you telling it what page you are currently on?

mike404055: 8 Years, 2 Months, 3 Weeks, 4 Days, 20 Hours, 51 Minutes ago

I've used this code and it works perfectly with the MySQLi extension, however it returns 128 pages with numbers in my case, which is far too long to use. How can I truncate the numbers so that only some are showing at any time? Someting like 1 2 3 4 5 6 7 8 9 10... 128 etc.

Many thanks,
Mike

Roland Rogers: 8 Years, 1 Month, 4 Weeks, 20 Hours, 39 Minutes ago

Great tutorial. I have same Qs as Andrew and Mike. What if recordset is filtered and showing fewer than all pages, and how to truncate list of numbers if pages is more than 10, for ex. ( 1|2|3|4|5 ... 24 )

I'll check forum to see if answers might lie there.

Thanks!

: 7 Years, 7 Months, 2 Weeks, 2 Minutes ago

hi, am getting a fatal error: Class 'WA_MySQLi_RS' not found in records.php on line 96 }. Am still very new to coding, please how should i go about it? you can reply me through assurance14453@gmail.com
Thanks

Nico: 7 Years, 7 Months, 1 Week, 1 Hour, 35 Minutes ago

Hi,
for all of you who asking how to use the pagination when filtering the query just do the following

REPLACE AFTER THE FROM PART
$NewsTotal->setQuery("SELECT count(1) a FROM news");
WITH WHAT YOUR INITIAL QUERY ACTUALLY FILTERS

SO FOR EXAMPLE IF YOU ACTUAL QUERY IS
SELECT products.ProductID, products.ProductSKU, products.ProductName, products.product_cat, products.product_collection_fgn_id, products.product_material_fgn_id, products.ProductPrice, products.product_on_discount, products.ProductRetailPrice, products.prroduct_on_retail_discount, products.ProductThumb, products.ProductImage, products.product_order, collections.collection_name, product_categories.product_category_name FROM products INNER JOIN collections ON products.product_collection_fgn_id = collections.collection_id INNER JOIN product_categories ON products.product_cat = product_categories.product_category_id WHERE products.product_collection_fgn_id = ? AND products.product_cat = ? ORDER BY products.ProductID DESC, products.product_order ASC, products.ProductName ASC"

TAKE THE FROM PART
FROM products INNER JOIN collections ON products.product_collection_fgn_id = collections.collection_id INNER JOIN product_categories ON products.product_cat = product_categories.product_category_id WHERE products.product_collection_fgn_id = ? AND products.product_cat = ?
AND PASTE IT INSIDE THE
$NewsTotal->setQuery("SELECT count(1) a FROM news");

SO YOUR FINAL QUERY SHOULD BE
$NewsTotal->setQuery("SELECT count(1) a FROM products INNER JOIN collections ON products.product_collection_fgn_id = collections.collection_id INNER JOIN product_categories ON products.product_cat = product_categories.product_category_id WHERE products.product_collection_fgn_id = ? AND products.product_cat = ?");

Just don't forget to place the filtering variables at the end
$getAllProducts->bindParam("i", "".$_GET['col'] ."", "-1"); //WAQB_Param1
$getAllProducts->bindParam("i", "".$_GET['cat'] ."", "-1"); //WAQB_Param1


Thank you.

PS I used a more complex query (more filters) to show you that it can work with as many filters as you like

Nico: 7 Years, 7 Months, 1 Week, 1 Hour, 34 Minutes ago

Just a correction to my previous post
The variables should be like this
$NewsTotal->bindParam("i", "".$_GET['col'] ."", "-1"); //WAQB_Param1
$NewsTotal->bindParam("i", "".$_GET['cat'] ."", "-1"); //WAQB_Param1

rating

Nikou: 7 Years, 2 Months, 3 Weeks, 4 Days, 23 Hours, 9 Minutes ago

Very nice job for pagination, Thanks a lot.

Build websites with a little help from your friends

Your friends over here at WebAssist! These Dreamweaver extensions will assist you in building unlimited, custom websites.

Build websites from already-built web applications

These out-of-the-box solutions provide you proven, tested applications that can be up and running now.  Build a store, a gallery, or a web-based email solution.

Want your website pre-built and hosted?

Everything else!

Close Windowclose

Rate your experience or provide feedback on this page

Account or customer service questions?
Please user our contact form.

Need technical support?
Please visit support to ask a question

Content

rating

Layout

rating

Ease of use

rating

security code refresh image

We do not respond to comments submitted from this page directly, but we do read and analyze any feedback and will use it to help make your experience better in the future.

go to page top