Speed up development with full-stack environments for every branch.

Learn More

PHP PDO pagination example [MySQL]

Forked from Serialize PDO instance Using __sleep().

2418 Runs 10379 Views 14170 Copies

Simple pagination example.

Saved

Saved

cerealk 13

cerealk
published 2 years ago

<?php

	$servername = "localhost";
	$dbname 	  = "test";
	$dbusername = "root";
	$dbpassword = "";
	$error	  	= FALSE;
	$result   	= FALSE;

	try {
		$conn = new PDO("mysql:host=$servername;dbname=$dbname", $dbusername, $dbpassword);
		$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

		$total  = $conn->query("SELECT COUNT(id) as rows FROM Posts")
				  ->fetch(PDO::FETCH_OBJ);

		$perpage = 3;
		$posts  = $total->rows;
		$pages  = ceil($posts / $perpage);

		# default
		$get_pages = isset($_GET['page']) ? $_GET['page'] : 1;

		$data = array(

			'options' => array(
				'default'   => 1,
				'min_range' => 1,
				'max_range' => $pages
				)
		);

		$number = trim($get_pages);
		$number = filter_var($number, FILTER_VALIDATE_INT, $data);
		$range  = $perpage * ($number - 1);

		$prev = $number - 1;
		$next = $number + 1;

		$stmt = $conn->prepare("SELECT ID, Author, Content FROM Posts LIMIT :limit, :perpage");
		$stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
		$stmt->bindParam(':limit', $range, PDO::PARAM_INT);
		$stmt->execute();

		$result = $stmt->fetchAll();

	} catch(PDOException $e) {
		$error = $e->getMessage();
	}

	$conn = null;
?>
<!DOCTYPE html>
<html>
	<head>
		<meta charset="utf-8">
		<meta name="viewport" content="width=device-width, initial-scale=1.0">
		<link rel="stylesheet" type="text/css" href="/style.css">
		<title>User View Page</title>
	</head>
	<body>

		<div id="wrap">

			<?php
			if($error)
			{
				echo "
				<div class=\"error\">
					<p><strong>Database Error:</strong> $error</p>
					<p><a href=\"/setup.php\">Go to Setup</a></p>
				</div>";
			}
			?>

			<div class="navigation">
			<?php
				
				if($result && count($result) > 0)
				{
					echo "<h3>Total pages ($pages)</h3>";

					# first page
					if($number <= 1)
						echo "<span>&laquo; prev</span> | <a href=\"?page=$next\">next &raquo;</a>";
					
					# last page
					elseif($number >= $pages)
						echo "<a href=\"?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";
					
					# in range
					else
						echo "<a href=\"?page=$prev\">&laquo; prev</a> | <a href=\"?page=$next\">next &raquo;</a>";
				}

				else
				{
					echo "<p>No results found.</p>";
				}
				
			?>
			</div>

			<?php

				if($result && count($result) > 0)
				{
					echo '
					<div class="posts">
						<h3>Posts</h3>
						<table>
							<thead>
								<tr>
									<th>ID
									<th>Author
									<th>Content
							<tbody>
					';
						foreach($result as $key => $row)
						{
							echo "
								<tr>
									<td>$row[ID]
									<td>$row[Author]
									<td>$row[Content]

							";
						}


					echo '
						</table>
					</div>
					';
				}

			?>
		</div>

	</body>
</html>
Please login/signup to get access to the terminal.

Your session has timed out.

Dismiss (the page may not function properly).