looking for an easy way to implement pagination

alexandre · November 8, 2022

i need something with php and html separated. when i look around google there is a lot of exmples but they all seems a bit messy.. if anyone knows an easy way to achieve this would be appreciated.

the less messy i could find yet looks like that:

<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
// If the user is not logged in redirect to the login page...
// Get the total number of records from our table "students".
$total_pages = $mysqli->query('SELECT * FROM random_donation_clash')->num_rows;

// Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;

// Number of results to show on each page.
$num_results_on_page = 25;

if ($stmt = $mysqli->prepare('SELECT * FROM random_donation_clash ORDER BY name LIMIT ?,?')) {
	// Calculate the page to get the results we need from our table.
	$calc_page = ($page - 1) * $num_results_on_page;
	$stmt->bind_param('ii', $calc_page, $num_results_on_page);
	$stmt->execute();
	// Get the results...
	$result = $stmt->get_result();
	?>
	<!DOCTYPE html>
	<html>
		<head>
			<title>PHP & MySQL Pagination by CodeShack</title>
			<meta charset="utf-8">
			<style>
			html {
				font-family: Tahoma, Geneva, sans-serif;
				padding: 20px;
				background-color: #F8F9F9;
			}
			table {
				border-collapse: collapse;
				width: 500px;
			}
			td, th {
				padding: 10px;
			}
			th {
				background-color: #54585d;
				color: #ffffff;
				font-weight: bold;
				font-size: 13px;
				border: 1px solid #54585d;
			}
			td {
				color: #636363;
				border: 1px solid #dddfe1;
			}
			tr {
				background-color: #f9fafb;
			}
			tr:nth-child(odd) {
				background-color: #ffffff;
			}
			.pagination {
				list-style-type: none;
				padding: 10px 0;
				display: inline-flex;
				justify-content: space-between;
				box-sizing: border-box;
			}
			.pagination li {
				box-sizing: border-box;
				padding-right: 10px;
			}
			.pagination li a {
				box-sizing: border-box;
				background-color: #e2e6e6;
				padding: 8px;
				text-decoration: none;
				font-size: 12px;
				font-weight: bold;
				color: #616872;
				border-radius: 4px;
			}
			.pagination li a:hover {
				background-color: #d4dada;
			}
			.pagination .next a, .pagination .prev a {
				text-transform: uppercase;
				font-size: 12px;
			}
			.pagination .currentpage a {
				background-color: #518acb;
				color: #fff;
			}
			.pagination .currentpage a:hover {
				background-color: #518acb;
			}
			</style>
		</head>
		<body>
			<table>
				<tr>
					<th>Name</th>
					<th>Age</th>
					<th>Join Date</th>
				</tr>
				<?php while ($row = $result->fetch_assoc()): ?>
				<tr>
					<td><?php echo $row['name']; ?></td>
					<td><?php echo $row['age']; ?></td>
					<td><?php echo $row['joined']; ?></td>
				</tr>
				<?php endwhile; ?>
			</table>
			<?php if (ceil($total_pages / $num_results_on_page) > 0): ?>
			<ul class="pagination">
				<?php if ($page > 1): ?>
				<li class="prev"><a href="pagination.php?page=<?php echo $page-1 ?>">Prev</a></li>
				<?php endif; ?>

				<?php if ($page > 3): ?>
				<li class="start"><a href="pagination.php?page=1">1</a></li>
				<li class="dots">...</li>
				<?php endif; ?>

				<?php if ($page-2 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page-2 ?>"><?php echo $page-2 ?></a></li><?php endif; ?>
				<?php if ($page-1 > 0): ?><li class="page"><a href="pagination.php?page=<?php echo $page-1 ?>"><?php echo $page-1 ?></a></li><?php endif; ?>

				<li class="currentpage"><a href="pagination.php?page=<?php echo $page ?>"><?php echo $page ?></a></li>

				<?php if ($page+1 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+1 ?>"><?php echo $page+1 ?></a></li><?php endif; ?>
				<?php if ($page+2 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+2 ?>"><?php echo $page+2 ?></a></li><?php endif; ?>

				<?php if ($page < ceil($total_pages / $num_results_on_page)-2): ?>
				<li class="dots">...</li>
				<li class="end"><a href="pagination.php?page=<?php echo ceil($total_pages / $num_results_on_page) ?>"><?php echo ceil($total_pages / $num_results_on_page) ?></a></li>
				<?php endif; ?>

				<?php if ($page < ceil($total_pages / $num_results_on_page)): ?>
				<li class="next"><a href="pagination.php?page=<?php echo $page+1 ?>">Next</a></li>
				<?php endif; ?>
			</ul>
			<?php endif; ?>
		</body>
	</html>
	<?php
	$stmt->close();
}
?>

and still i have no idea how i can implement this to my code without breakig everything .. so any advice is greatly welcome.

requinix · November 8, 2022

1. Instead of doing lots of calculations everywhere, especially the same ones over and over again, use variables.
2. When you just want to echo something, use short open tags.
3. Clean up the code. For example, your "$total_pages" is not actually the total number of pages. And when you're comparing X+1 versus Y+1, having those two +1s is pointless.

IMO the basic style you have there, where you have some PHP code in the markup but it's just outputs and simple conditions, is fine - it's simply messy. Like this

<?php if ($page+1 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+1 ?>"><?php echo $page+1 ?></a></li><?php endif; ?>

isn't as easy to read as this

<?php if ($page < $total_pages): ?><li class="page"><a href="pagination.php?page=<?= $nextpage ?>"><?= $nextpage ?></a></li><?php endif; ?>

but you can make it look even cleaner by separating the HTML and the PHP even more, like

<?php if ($page < $total_pages): ?>
	<li class="page"><a href="pagination.php?page=<?= $nextpage ?>"><?= $nextpage ?></a></li>
<?php endif; ?>

alexandre · November 8, 2022

3 minutes ago, requinix said:
1. Instead of doing lots of calculations everywhere, especially the same ones over and over again, use variables.
2. When you just want to echo something, use short open tags.
3. Clean up the code. For example, your "$total_pages" is not actually the total number of pages. And when you're comparing X+1 versus Y+1, having those two +1s is pointless.

IMO the basic style you have there, where you have some PHP code in the markup but it's just outputs and simple conditions, is fine - it's simply messy. Like this
<?php if ($page+1 < ceil($total_pages / $num_results_on_page)+1): ?><li class="page"><a href="pagination.php?page=<?php echo $page+1 ?>"><?php echo $page+1 ?></a></li><?php endif; ?>
isn't as easy to read as this
<?php if ($page < $total_pages): ?><li class="page"><a href="pagination.php?page=<?= $nextpage ?>"><?= $nextpage ?></a></li><?php endif; ?>
but you can make it look even cleaner by separating the HTML and the PHP even more, like
<?php if ($page < $total_pages): ?>
	<li class="page"><a href="pagination.php?page=<?= $nextpage ?>"><?= $nextpage ?></a></li>
<?php endif; ?>

yeah the example you just shown look much more easier to read but to do this i will have to get a better understanding of this code.. this isnt written by me and i think it looks terribly messy, even with the tutorial i wasnt able to make it work with my code but thank you for the precisions.

mac_gyver · November 8, 2022

here's an additional list of points for this code, most of which will clean up, organizing, and simplify the code -

put any error related settings in the php.ini on your system.
build any sql query in a php variable. this makes debugging easier and helps prevent typo mistakes.
pagination involves two queries. the common part of the query, from the FROM term through to any HAVING term should be built in a php variable, with a corresponding array of any prepared query input parameters, so that these common parts can be used in both queries.
don't select all the columns and all the rows to find the total number of matching rows. use a SELECT COUNT(*) AS total... query, then fetch and use the total value.
use the much simpler PDO database extension.
as already mentioned, calculate the total number of pages, once, and store it in a correctly named php variable. the total number of pages is needed to validate/limit the submitted page number and in the pagination link code.
use exceptions for database statement errors and only handle the exception/error in your code for user recoverable errors, of which a select query isn't, i.e. don't put any error handing logic in your code for a select query.
put any query to get/produce the data needed to display the page above the start of the html document, fetch all the data from the query into an appropriately named php variable, then test/loop over this variable at the appropriate location in the html document.
you should list out the columns you are selecting in a query.
if the data retrieval query doesn't match any rows of data, test and output a message stating so, rather than to output nothing at that point in the html document.
put any code to get/product the pagination links above the start of the html document, build the output in an appropriately named php variable, then echo this variable at the appropriate location(s) in the html document.
when building pagination links, you should start with a copy of any existing $_GET parameters, set the current 'page' parameter value for the current link, then build the query string part of the current URL by using php's http_build_query().
there's generally no need to close prepared statements, free result sets, or close database connections in your code since php will destroy these things when your script ends.

mac_gyver · November 8, 2022

here's an example showing the points that have been made -

<?php

// initialization

// this example uses the much simpler PDO database extension
require 'pdo_connection.php';

// number of results per logic page
$num_results_on_page = 25;

// number of +/- pagination links around the current page number
$range = 3;

// post method form processing - none in this example

// get method business logic - get/produce data needed to display the page

// build the common part of the queries
$common_sql = 'FROM random_donation_clash';

// get the total number of matching rows
$sql = "SELECT COUNT(*) $common_sql";
$stmt = $pdo->query($sql);
$total_rows = $stmt->fetchColumn();

// calculate total number of pages
$total_pages = ceil($total_rows / $num_results_on_page);

// get/condition the current page number, default to page 1
$page = intval($_GET['page'] ?? 1);

// limit page to be between 1 and $total_pages
$page = max(1,min($total_pages,$page));

// get a logical page of data
$offset = ($page - 1) * $num_results_on_page;
// note: a person's age is not a fixed number, unless they are dead. store the date of birth and calculate the age when needed.
$sql = "SELECT name, age, joined $common_sql ORDER BY name LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $offset, $num_results_on_page ]);
$page_data = $stmt->fetchAll();

// build pagination links: prev, 1, range around current page, total_pages, next
$pagination_links = '';
if($total_pages > 1)
{
	// produce array of pagination numbers: 1, range around current page, total_pages, without duplicates, between 1 and total_pages
	$links = array_filter(array_unique(array_merge([1],range($page-$range, $page+$range),[$total_pages])),
	function ($val) use ($total_pages) { return $val >= 1 && $val <= $total_pages; });

	// get a copy of any existing get parameters
	$get = $_GET;

	// produce previous
	$get['page'] = $page - 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == 1 ? 'prev ' : "<a href='?$qs'>prev</a> ";

	// produce numerical links
	foreach($links as $link)
	{
		$get['page'] = $link;
		$qs = http_build_query($get,'', '&amp;');
		$pagination_links .= $link == $page ? "$link " : "<a href='?$qs'>$link</a> ";
	}

	// produce next
	$get['page'] = $page + 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == $total_pages ? 'next' : "<a href='?$qs'>next</a>";
}

// html document
?>
<!DOCTYPE html>
<html lang="en-US">
	<head>
		<meta charset="utf-8">
		<title>Pagination Example</title>
	</head>
	<body>
	<?php if(!$page_data): ?>
		<p>There is no data to display</p>
	<?php else: ?>
		<table>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>Join Date</th>
			</tr>
			<?php foreach($page_data as $row): ?>
			<tr>
				<td><?=$row['name']?></td>
				<td><?=$row['age']?></td>
				<td><?=$row['joined']?></td>
			</tr>
			<?php endforeach; ?>
		</table>
	<?php endif; ?>
	
	<p><?=$pagination_links?></p>
	</body>
</html>

alexandre · November 8, 2022

4 minutes ago, mac_gyver said:

here's an example showing the points that have been made -

<?php

// initialization

// this example uses the much simpler PDO database extension
require 'pdo_connection.php';

// number of results per logic page
$num_results_on_page = 25;

// number of +/- pagination links around the current page number
$range = 3;

// post method form processing - none in this example

// get method business logic - get/produce data needed to display the page

// build the common part of the queries
$common_sql = 'FROM random_donation_clash';

// get the total number of matching rows
$sql = "SELECT COUNT(*) $common_sql";
$stmt = $pdo->query($sql);
$total_rows = $stmt->fetchColumn();

// calculate total number of pages
$total_pages = ceil($total_rows / $num_results_on_page);

// get/condition the current page number, default to page 1
$page = intval($_GET['page'] ?? 1);

// limit page to be between 1 and $total_pages
$page = max(1,min($total_pages,$page));

// get a logical page of data
$offset = ($page - 1) * $num_results_on_page;
// note: a person's age is not a fixed number, unless they are dead. store the date of birth and calculate the age when needed.
$sql = "SELECT name, age, joined $common_sql ORDER BY name LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $offset, $num_results_on_page ]);
$page_data = $stmt->fetchAll();

// build pagination links: prev, 1, range around current page, total_pages, next
$pagination_links = '';
if($total_pages > 1)
{
	// produce array of pagination numbers: 1, range around current page, total_pages, without duplicates, between 1 and total_pages
	$links = array_filter(array_unique(array_merge([1],range($page-$range, $page+$range),[$total_pages])),
	function ($val) use ($total_pages) { return $val >= 1 && $val <= $total_pages; });

	// get a copy of any existing get parameters
	$get = $_GET;

	// produce previous
	$get['page'] = $page - 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == 1 ? 'prev ' : "<a href='?$qs'>prev</a> ";

	// produce numerical links
	foreach($links as $link)
	{
		$get['page'] = $link;
		$qs = http_build_query($get,'', '&amp;');
		$pagination_links .= $link == $page ? "$link " : "<a href='?$qs'>$link</a> ";
	}

	// produce next
	$get['page'] = $page + 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == $total_pages ? 'next' : "<a href='?$qs'>next</a>";
}

// html document
?>
<!DOCTYPE html>
<html lang="en-US">
	<head>
		<meta charset="utf-8">
		<title>Pagination Example</title>
	</head>
	<body>
	<?php if(!$page_data): ?>
		<p>There is no data to display</p>
	<?php else: ?>
		<table>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>Join Date</th>
			</tr>
			<?php foreach($page_data as $row): ?>
			<tr>
				<td><?=$row['name']?></td>
				<td><?=$row['age']?></td>
				<td><?=$row['joined']?></td>
			</tr>
			<?php endforeach; ?>
		</table>
	<?php endif; ?>
	
	<p><?=$pagination_links?></p>
	</body>
</html>

thanks a lot for your instructions. and i get what you mean by the age isnt a fixed number , and this isnt going in my code , i just got angry not understanding that example and never finished to make the adjustement , i was more looking to get an example that would not be mixing php and html document like this one. usualy i have been using only the php side of the file to display and process. i dont like playing with the html document since it can get messy real quick and if i am making my script exit, the html code wont show up so i prefer doing all in php. just a little example of my data being pulled and displayed in my code:

if ($donationclash == true) {
$donationclashcompleted = false;

$stmt = "SELECT total_participant, sum(total_participant) as total_participant FROM random_donation_clash";
 $num_participant = mysqli_query($con, $stmt);
 if (mysqli_num_rows($num_participant) > 0) {
		 while ($num_row = mysqli_fetch_assoc($num_participant)) {
  
			$participant_num = $num_row['total_participant'];
}}
$stmt = 'SELECT total_donated, sum(donation) AS total_donated FROM random_donation_clash';
$WINNERS_1 = mysqli_query($con, $stmt);
if (mysqli_num_rows($WINNERS_1) > 0 ) {
    while ($WINNERS_ROW1 = mysqli_fetch_assoc($WINNERS_1)) {
			$total_participations_amount = $WINNERS_ROW1['total_donated'];
		}}
		$stmt = 'SELECT total_received, sum(total_received) AS total_received FROM random_donation_clash WHERE total_participant = 1';
		$WINNERS_2 = mysqli_query($con, $stmt);
		if (mysqli_num_rows($WINNERS_2) > 0 ) {
		    while ($WINNERS_ROW2 = mysqli_fetch_assoc($WINNERS_2)) {
					$total_received_amount = $WINNERS_ROW2['total_received'];
				}}
				echo "	<div class='rankingtrue2'>
				<table class='rankingtable4'><tr>
							<th>Total participants</th><th>total donated by the participants</th><th>total received by the participants</th>
					</tr>
					<tr>
							<td class='remain1'>". $participant_num  . "</td>
							<td class='remain2'>". $total_participations_amount . "</td>
								<td class='remain4'>". $total_received_amount . "</td>
					</tr></table></div><br><br><br>";
	$stmt = 'SELECT DISTINCT users_name, donation, total_donated, total_received FROM random_donation_clash GROUP BY participation_id ORDER BY total_received DESC';
	$result = mysqli_query($con, $stmt);
	if (mysqli_num_rows($result) > 0) {
	    while ($result_row = mysqli_fetch_assoc($result)) {
				echo"<div class='rankingtrue2'>
				<div class='ranking_wrapper'>
					 <table class='rankingtable3'>

	            <tr>
	               <td>username</td><td>total donated</td><td>total_received</td>
	            </tr>
	            <tr>
	                <td class='remain1'>" . $result_row['users_name']. "</td>

	                <td class='remain3'>" . $result_row['total_donated']. "</td>
                  <td class='remain4'>" . $result_row['total_received']. "</td>
	            </tr></table></div></div><br><br>";
}
}

i am not touching the html sidee at all.. is that possible or it is a bad practice to do so?

ginerjm · November 8, 2022

The trick that I use is to always use a single function to display my page once I am done using php to generate the dynamic content. As recommended above I use php vars to assemble all of that and then my function uses references those vars globallly to display those results where they need to fit in to the html. Pretty much I have a nice html template that has all of my most common features for any page that I develop so it's simply a matter of getting the inputs of the form being displayed and creating the desired output from them and creating a var to hold the query results (an html table or a select tag, etc.) and then call my displaypage function and then exit.

PS - I ALWAYS use the heredocs construct to build my html content that way the php vars fit right without having to use any php tag EVER. Check it out in the manual. It is invaluable.

alexandre · November 8, 2022

9 minutes ago, ginerjm said:

The trick that I use is to always use a single function to display my page once I am done using php to generate the dynamic content. As recommended above I use php vars to assemble all of that and then my function uses references those vars globallly to display those results where they need to fit in to the html. Pretty much I have a nice html template that has all of my most common features for any page that I develop so it's simply a matter of getting the inputs of the form being displayed and creating the desired output from them and creating a var to hold the query results (an html table or a select tag, etc.) and then call my displaypage function and then exit.

PS - I ALWAYS use the heredocs construct to build my html content that way the php vars fit right without having to use any php tag EVER. Check it out in the manual. It is invaluable.

yes it seems like a nice way to go about it , do you have a sample of that function that you could show here or it is a privacy matter ?

ginerjm · November 8, 2022

You know - you don't really have to repeat every post when you make a response.....

Here is the sample

//*******************************
//*******************************
function DisplayPage()
{
	global $action, $errmsg, $hide, $focusid, $ah_php_path, $show_div_titles, $show_borders;
	/*   hide is to turn on/off input tags that are hidden
		ah_php_path is the path to my saved modules that get used here
		The show.... vars are to enable showing the titles of my div tags and their borders if 
		I am rearranging things during page design.  
		focusid is used by my php to set focus on different inputs 
	*/
	if ($show_div_titles)
		$div_title = '';
	else
		$div_title = "title=''";
	if ($show_borders)
		$border = " style='border:1px solid white; '";
	else
		$border = '';
	$code=<<<heredocs
	<!DOCTYPE html>
	<html lang='en'>
    <head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
	<meta name="viewport" content="width=device-width,initial-scale=1">
	<title>xxx</title>
	<style type="text/css">
	body
	{
		height:100%;
		width: 100%;
		margin: 0px;
		padding: 0px;
		border: none;
		background-color:#c0c0c0;
		font-family:'Segoe UI','Trebuchet MS',Tahoma,Arial,Sans-serif;
	}
	p, h1, h2, h3, h4, h5, h6
	{
		margin:0px;
		padding:0px;
	}
	.bold {font-weight:600;}
	.red { color:red;}
	.black {color:black;}
	.yellow {color:yellow;}
	.orange 
	{
		color:#f71602; 
		font-weight:800;
	}
	label,input	{font-size:16px;}
	input
	{
		color:black;
		background-color:white;
	}
	.smltxt {font-size:12px;}
	.regtxt {font-size:18px;}
	.medtxt {font-size:22px;}
	.lgtxt {font-size:26px;}
	#form_box
	{
		position:relative;
		float:left;
		margin:1% 1%;
		padding:10px;
		border:1px solid yellow;
	}
/*	MEDIA QUERY */
/*	MEDIA QUERY */
/*	MEDIA QUERY */
@media screen and (max-width: 600px)
{
#form_box
{
	position:relative;
	float:left;
	margin:1% 1%;
	padding:10px;
	border:1px solid yellow;
}
label,input{font-size:14px;}
.smltxt {font-size:12px;}
.regtxt {font-size:16px;}
.medtxt {font-size:20px;}
.lgtxt {font-size:24px;}
}
 /* end of media query */
	</style>
heredocs;
	echo $code;
	require $ah_php_path . 'js/setFocus.js';
	$code =<<<heredocs
	</head>
	<body bgcolor='#c0c0c0' onload='setFocus("$focusid")'>
	<center>
	<span class='lgtxt'>
		xxx Page Title
	</span>
	</center>
	<br>
	<span class='red regtxt'>
	$errmsg
	</span>
	<div id='form_box' $border $div_title title='form_box'>
	<form name='thisform' method='POST' action='$action' autocomplete='off'>
	<input type='submit' name='btn' id='rtnbtn' value='Return' accesskey='r'>
	</form>
	</div>
	<br>
	</body>
	</html>
heredocs;
	echo $code;
}

Any new divs of forms that you create with your php you place into a var and then add that var to the global line and put it where you want that code to appear. I do very little php in this function as you can see.

alexandre · November 8, 2022

yes sorry for repeating the messages, it was kind of to show that i was answering you. and i think that i will have to go look at heredocs manual to understand your function but it looks interesting, i dont really understand how you get to not have to be using php tags everywhere in your html i will look more into this.

alexandre · November 8, 2022

there is something i am wondering ..

how many rows like that would it take to make a page hard to load , if there is nothing else than that on the page, do i really need to make a pagination or an easier way would be to simply make a 'load more' button instead. i dont expect the users to oftenly go through tons of records but more that they look for their own records so from there i might want to find a way to make anchors to their names so i could use the search bar to get their record.

Strider64 · November 8, 2022

Another way of doing pagination is to figure out the offset of the table meaning where you are at actually in the database table:

$offset = $per_page * ($current_page - 1);

$sql = 'SELECT * FROM myTable WHERE category =:category ORDER BY id DESC, date_added DESC LIMIT :perPage OFFSET :offset'; // Offset of the table
        $stmt = Database::pdo()->prepare($sql); // Prepare the query:
        $stmt->execute(['perPage' => $per_page, 'category' => $category, 'offset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

total pages in this case would be the number of records in the category

$total_pages = ceil($this->total_count / $this->per_page);

Edited November 8, 2022 by Strider64

alexandre · November 8, 2022

20 minutes ago, Strider64 said:

Another way of doing pagination is to figure out the offset of the table meaning where you are at actually in the database table:

$offset = $per_page * ($current_page - 1);

$sql = 'SELECT * FROM myTable WHERE category =:category ORDER BY id DESC, date_added DESC LIMIT :perPage OFFSET :offset'; // Offset of the table
        $stmt = Database::pdo()->prepare($sql); // Prepare the query:
        $stmt->execute(['perPage' => $per_page, 'category' => $category, 'offset' => $offset]); // Execute the query with the supplied data:
        return $stmt->fetchAll(PDO::FETCH_ASSOC);

total pages in this case would be the number of records in the category

$total_pages = ceil($this->total_count / $this->per_page);

if this is all you need to put in place your pagination , it seems like a cleaner way to do it. also i would like to know if this is a problem to use mysqli and pdo for different queries. would it interfere or have unexpected behavior or it would simply not work?

mac_gyver · November 8, 2022

2 hours ago, Strider64 said:

Another way of doing pagination is to figure out the offset of the table meaning where you are at actually in the database table:

this is the same as what the example code is doing, just with a variation of the LIMIT/OFFSET term.

2 hours ago, alexandre said:

if this is all you need to put in place your pagination

it's not. this is only the data retrieval query part of the process. you still need all the other functionality that was shown in the example code.

alexandre · November 8, 2022

20 minutes ago, mac_gyver said:

this is the same as what the example code is doing, just with a variation of the LIMIT/OFFSET term.

it's not. this is only the data retrieval query part of the process. you still need all the other functionality that was shown in the example code.

yeah thats what i was thinking.. but i decided to simply limit the number of records to 50 and add a search bar to search for any users records. i will also add a button to display directly the user its own record maybe along with calculating the number of records before his, so it would give the user his position in the ranking. there is just one problem left and it is that the number of winners is going up by one for every 10 participants registered, so if i limit the number of rows to 50, the number of winners can get higher and not display some records that others below this rank need to see to keep up.. from where i would need that load more button instead of this pagination. i think javascript it will be in this case, i dont think i will have a choice unless anyone have an alternative to ajax to make a load more button?

Edited November 8, 2022 by alexandre

alexandre · November 9, 2022

7 hours ago, mac_gyver said:

here's an example showing the points that have been made -

<?php

// initialization

// this example uses the much simpler PDO database extension
require 'pdo_connection.php';

// number of results per logic page
$num_results_on_page = 25;

// number of +/- pagination links around the current page number
$range = 3;

// post method form processing - none in this example

// get method business logic - get/produce data needed to display the page

// build the common part of the queries
$common_sql = 'FROM random_donation_clash';

// get the total number of matching rows
$sql = "SELECT COUNT(*) $common_sql";
$stmt = $pdo->query($sql);
$total_rows = $stmt->fetchColumn();

// calculate total number of pages
$total_pages = ceil($total_rows / $num_results_on_page);

// get/condition the current page number, default to page 1
$page = intval($_GET['page'] ?? 1);

// limit page to be between 1 and $total_pages
$page = max(1,min($total_pages,$page));

// get a logical page of data
$offset = ($page - 1) * $num_results_on_page;
// note: a person's age is not a fixed number, unless they are dead. store the date of birth and calculate the age when needed.
$sql = "SELECT name, age, joined $common_sql ORDER BY name LIMIT ?,?";
$stmt = $pdo->prepare($sql);
$stmt->execute([ $offset, $num_results_on_page ]);
$page_data = $stmt->fetchAll();

// build pagination links: prev, 1, range around current page, total_pages, next
$pagination_links = '';
if($total_pages > 1)
{
	// produce array of pagination numbers: 1, range around current page, total_pages, without duplicates, between 1 and total_pages
	$links = array_filter(array_unique(array_merge([1],range($page-$range, $page+$range),[$total_pages])),
	function ($val) use ($total_pages) { return $val >= 1 && $val <= $total_pages; });

	// get a copy of any existing get parameters
	$get = $_GET;

	// produce previous
	$get['page'] = $page - 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == 1 ? 'prev ' : "<a href='?$qs'>prev</a> ";

	// produce numerical links
	foreach($links as $link)
	{
		$get['page'] = $link;
		$qs = http_build_query($get,'', '&amp;');
		$pagination_links .= $link == $page ? "$link " : "<a href='?$qs'>$link</a> ";
	}

	// produce next
	$get['page'] = $page + 1;
	$qs = http_build_query($get,'', '&amp;');
	$pagination_links .= $page == $total_pages ? 'next' : "<a href='?$qs'>next</a>";
}

// html document
?>
<!DOCTYPE html>
<html lang="en-US">
	<head>
		<meta charset="utf-8">
		<title>Pagination Example</title>
	</head>
	<body>
	<?php if(!$page_data): ?>
		<p>There is no data to display</p>
	<?php else: ?>
		<table>
			<tr>
				<th>Name</th>
				<th>Age</th>
				<th>Join Date</th>
			</tr>
			<?php foreach($page_data as $row): ?>
			<tr>
				<td><?=$row['name']?></td>
				<td><?=$row['age']?></td>
				<td><?=$row['joined']?></td>
			</tr>
			<?php endforeach; ?>
		</table>
	<?php endif; ?>
	
	<p><?=$pagination_links?></p>
	</body>
</html>

i could use this pagination example here but the pdo extension cannot connect i think because the rest of my code is using mysqli extension . is it forced to be with pdo extension or there is a way to convert it to mysqli extension?

Edited November 9, 2022 by alexandre

mac_gyver · November 9, 2022

the number of concurrent database connections is only limited by your database server/hosting, and is typically several 100. your script could (but shouldn't) make a dozen connections using both mysqli and PDO at the same time.

the PDO extension is better designed, simpler, and more consistent than the mysqli extension. as an added benefit, works with 12 different database types, so you are not learning a new set of php statements for each database type.

if you had some problem using the PDO extension, you would need to find out why and fix it.

the following is typical PDO connection code -

$DB_HOST = ''; // database host name or ip address
$DB_USER = ''; // database username
$DB_PASS = ''; // database password
$DB_NAME = ''; // database name
$DB_ENCODING = 'utf8mb4'; // db character encoding. set to match your database table's character set. note: utf8 is an alias of utf8mb3/utf8mb4

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // set the error mode to exceptions
			PDO::ATTR_EMULATE_PREPARES => false, // run real prepared queries
			PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // set default fetch mode to assoc
			];

$pdo = new pdo("mysql:host=$DB_HOST;dbname=$DB_NAME;charset=$DB_ENCODING",$DB_USER,$DB_PASS,$options);

Edited November 9, 2022 by mac_gyver

alexandre · November 9, 2022

<?php
// DB credentials.
define('DB_HOST','localhost');
define('DB_USER','root');
define('DB_PASS','');
define('DB_NAME',');
// Establish database connection.
try
{
$pdo = new PDO("mysql:host=".DB_HOST.";dbname=".DB_NAME,
DB_USER, DB_PASS,
array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8mb4'"));
}
catch (PDOException $e)
{
exit("Error: " . $e->getMessage());
}

this is the connection file i am using to connect but it doesnt work maybe it is where i placed it earlier in my code but , tomorrow i will try to redo it. gotta stop for tonight , my right eye is now blurry for some reason ..

Sign In

looking for an easy way to implement pagination

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information