jQuery PHP Pagination
Learn how to add pagination to your website using jQuery, PHP, and MySQL. Easy steps for new developers to show data in pages and make sites faster.
Hi there! If you're new to web development, you might have noticed a problem: showing a long list of items on one page. It makes the page slow and hard to read. That's where pagination comes in. Pagination means splitting your list into smaller pages, like page 1, page 2, and so on. Users can click to see more without loading everything at once.
In this guide, we'll learn how to create pagination using jQuery for the front end, PHP for the back end, and MySQL to store data. This is perfect for things like user lists, blog posts, or product catalogs. We'll keep it super simple, step by step, using easy words. No complicated stuff here! By the end, you'll understand the logic, see examples, and get tips to make it even better.
Why is this useful? Suppose your website has 1000 users. Instead of showing them all at once, you can display 10 users on each page. Users click "next" to see more. It loads faster and looks clean. Plus, search engines like Google love fast sites, so this helps with SEO too.
Let's dive in. We'll need a few files: config.php for database connection, index.php to show the page, data.php to fetch data, pagination.js for jQuery magic, and style.css for styling. We'll explain each one.
Why Do We Need Pagination?
Think about a real-life example. You have an online shop with 500 products. If you load all on one page, it takes forever to open. Users get bored and leave. Pagination fixes this by showing, say, 20 products per page. It's like flipping pages in a book.
Another example: A blog with old posts. Without pagination, the home page is too long. With it, show 5 posts per page. Readers can go to older pages easily.
Here are some scenarios where pagination helps:
- Social media feeds: Show recent posts first, paginate older ones.
- Admin panels: List all orders or users without crashing the browser.
- Search results: Like Google, show 10 results per page.
The logic is simple: Count total items from the database. Divide by items per page to know how many pages. Then, use PHP to fetch only the needed items for the current page. jQuery handles clicks without reloading the whole page, making it smooth.
Tip: Always think about user experience. Too many items per page? Slow. Too few? Too many clicks. Test with 5-20 items per page.
What You Need Before Starting
This guide is for beginners, so make sure you have:
- A local server like XAMPP or WAMP (it includes PHP and MySQL).
- Basic knowledge of HTML, PHP, and JavaScript.
- jQuery library (we'll link it from Google).
If you're new, don't worry. We'll explain everything. No fancy tools needed.
Step 1: Set Up the Database
First, we need a place to store data. We'll use MySQL. Open phpMyAdmin or your database tool.
Create a table called "users". Here's the code:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(200) NOT NULL,
`Middlename` varchar(200) NOT NULL,
`LastName` varchar(200) NOT NULL,
PRIMARY KEY (`id`)
);
This creates a simple table with an ID and names. The ID auto-increases, so you don't need to set it manually.
Now, add some sample data to test. This is called "dumping data":
INSERT INTO `users` (`id`, `FirstName`, `Middlename`, `LastName`) VALUES
(1, 'John', 'K', 'Doe'),
(2, 'Kevin', 'M', 'Smith'),
(3, 'Kane', 'K', 'Parle'),
(4, 'Novic', 'T', 'Ray'),
(5, 'Rod', 'H', 'Knee'),
(6, 'Jack', 'N', 'Ryder'),
(7, 'Rosita', 'W', 'Siegl'),
(8, 'Nina', 'R', 'Williams'),
(9, 'Anto', 'S', 'Velten'),
(10, 'Emma', 'L', 'Stone'),
(11, 'Liam', 'J', 'Noah'),
(12, 'Olivia', 'E', 'James');
We added 12 users to make it fun. For pagination, we'll set 3 users per page, so that's 4 pages total.
Tip: In real projects, use real data. For testing, fake names work fine. Tools like Faker can generate more data.
Common issue: If table creation fails, check if the database exists. Create one called "test" first.
Step 2: Connect to Database (config.php)
This file holds the database connection details. Keep it secure!
<?php
$hostname = "localhost";
$username = "root";
$password = "password"; // Change if you have one
$database = "test";
$connection = new mysqli($hostname, $username, $password, $database);
if ($connection->connect_errno) {
echo "Failed to connect: " . $connection->connect_error;
exit();
}
?>
Logic: We use mysqli to connect to MySQL. If it fails, show an error. In live sites, don't show errors to users—log them instead.
Tip: For security, use prepared statements later to avoid SQL injection. For this simple guide, this is okay.
Step 3: The Main Page (index.php)
This is where users see the list. We calculate total pages here.
Note: We use "SELECT COUNT(*)" because it's fast. It counts rows without fetching all data.
<?php
include('config.php');
$per_page = 3; // Change this to show more or less
$sql = "SELECT COUNT(*) FROM users";
$result = $connection->query($sql);
$count = $result->fetch_row();
$pages = ceil($count[0] / $per_page);
?>
<!DOCTYPE html>
<html>
<head>
<title>jQuery PHP Pagination Example</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="pagination.js"></script>
<link rel="stylesheet" href="style.css">
</head>
<body>
<div class="center">
<h2>Simple Pagination Demo</h2>
<div id="content"></div>
<ul id="pagination">
<?php for ($i = 1; $i <= $pages; $i++) {
echo '<li id="' . $i . '">' . $i . '</li>';
} ?>
</ul>
<div id="loading"></div>
</div>
</body>
</html>
Logic: PHP creates the page links. jQuery loads data when clicked. We used a newer jQuery version for safety.
Example: With 12 users and 3 per page, you get 4 pages. Clicking "2" loads page 2 data.
Scenario: If no data, $pages=0. Add a check to show "No records".
Step 4: Fetch Data (data.php)
This file is called by jQuery. It returns the table for the current page.
<?php
include('config.php');
$per_page = 3;
$page = (isset($_GET['page']) && is_numeric($_GET['page'])) ? $_GET['page'] : 1;
$start = ($page - 1) * $per_page;
$sql = "SELECT * FROM users ORDER BY id LIMIT $start, $per_page";
$result = $connection->query($sql);
?>
<table class="user-table">
<tr><th>ID</th><th>First Name</th><th>Middle Name</th><th>Last Name</th></tr>
<?php while ($row = $result->fetch_array()) { ?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['FirstName']; ?></td>
<td><?php echo $row['Middlename']; ?></td>
<td><?php echo $row['LastName']; ?></td>
</tr>
<?php } ?>
</table>
Logic: LIMIT skips previous pages and takes only $per_page rows. For page 2, start=3, take 3 rows (4-6).
Tip: For large tables, add indexes on ID for speed. Escape user input to prevent hacks.
Another way: Add a search form to filter names, then paginate the results.
Step 5: Make It Interactive (pagination.js)
jQuery makes it smooth by loading data without refreshing the page.
$(document).ready(function() {
function showLoading() {
$("#loading").fadeIn(100).html('');
}
function hideLoading() {
$("#loading").fadeOut('slow');
}
$("#pagination li:first").css({ 'color': '#FF0084', 'border': 'none' });
$("#content").load("data.php?page=1", hideLoading);
$("#pagination li").click(function() {
showLoading();
$("#pagination li").css({ 'border': 'solid #ddd 1px', 'color': '#0063DC' });
$(this).css({ 'color': '#FF0084', 'border': 'none' });
var pageNum = this.id;
$("#content").load("data.php?page=" + pageNum, hideLoading);
});
});
Logic: On click, highlight the current page and load data.php with the page number using AJAX. Show a loading gif while waiting.
Example: Click page 3, it fetches rows 7-9 quickly.
Tip: Add "next" and "prev" buttons using page+1 or page-1 logic.
Common issue: If jQuery fails, check the browser console for errors. Maybe the URL is wrong.
Step 6: Add Styles (style.css)
Make it look good with some CSS.
body { font-family: Verdana; font-size: 15px; margin: 0; padding: 0; }
a { text-decoration: none; color: #b2b2b2; }
a:hover { color: #df3d82; text-decoration: underline; }
.center { text-align: center; margin-top: 50px; }
#loading { width: 100%; position: absolute; }
#pagination { list-style: none; display: inline-block; }
#pagination li { float: left; margin-right: 16px; padding: 5px; border: solid 1px #ddd; color: #0063dc; cursor: pointer; }
#pagination li:hover { color: #ff0084; }
.user-table { width: 800px; border: 1px solid #98bf21; margin-top: 20px; }
.user-table th { background: #a7c942; border: 1px solid #98bf21; }
.user-table td { border: 1px solid #98bf21; }
.user-table tr:nth-child(odd) { background: #eaf2d3; }
Logic: CSS adds striped rows for readability. Pagination links look like buttons.
Tip: For mobile, use media queries to make the table responsive.
More Tips and Ideas
Want to make your pagination better? Try these:
- Add search: Let users type a name, then paginate filtered results.
- Handle empty pages: If no data, show "No users found".
- Performance: For millions of rows, use efficient queries. Avoid "*" if not needed.
- Alternatives: Pure PHP pagination reloads the page. jQuery makes it AJAX-based and modern.
- Modern twist: Try Vue.js or React for fancier pagination, but start here.
- SEO tip: Add rel="next" and rel="prev" in links for search engines.
- Common mistake: Wrong page calculations. Test with odd numbers, like 10 items, 3 per page.
Case study: A forum site used pagination to cut load time from 10 seconds to 1 second. Users stayed longer!
Engage: Try adding sorting. Click a header to sort by name, then paginate.
Wrapping Up
You now know how to add pagination to your site. It's simple: Database for data, PHP for logic, jQuery for smooth clicks. Practice by changing per_page or adding more data.
This setup is beginner-friendly and works well for small sites. For big ones, learn about caching or databases like MongoDB.
Thanks for reading! If you have questions, comment below. Happy coding!
Download Source Code
0 Comments