PHP Search and Pagination using PDO

Search is an important functionality of any information-centric application. Pagination is an integral part of the display when voluminous records are displayed. In this tutorial, we are going implement search and pagination for a list page using PDO.

In a previous tutorial, we have already learned how to do database CRUD using PDO.

In this example, we have a general search to filter all the column data by the specified keyword. For pagination, we have limited the record count as three per page and the page navigation links are specified at the bottom of the list.

View Demo

General Search Option

The general search input on the top right corner is used to do the search among all the columns of a row. If a match is found then that particular row will be filtered as a search result.

The code is,

$search_keyword = '';
if(!empty($_POST['search']['keyword'])) {
	$search_keyword = $_POST['search']['keyword'];
}
$sql = 'SELECT * FROM posts WHERE post_title LIKE :keyword OR description LIKE :keyword OR post_at LIKE :keyword ORDER BY id DESC ';
...
...
$pdo_statement = $pdo_conn->prepare($query);
$pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();

List Records with Pagination

We send a request for a page. Using this request we compute the select query limit to read data. We specified the number of records per page as constant in the beginning of this example program.

We calculate the page count by using the total records count in the database and the per page limit. Based on these calculations, we have created pagination link and displayed in the bottom of the list. The code is,

$per_page_html = '';
$page = 1;
$start=0;
if(!empty($_POST["page"])) {
	$page = $_POST["page"];
	$start=($page-1) * ROW_PER_PAGE;
}
$limit=" limit " . $start . "," . ROW_PER_PAGE;
$pagination_statement = $pdo_conn->prepare($sql);
$pagination_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pagination_statement->execute();

$row_count = $pagination_statement->rowCount();
if(!empty($row_count)){
	$per_page_html .= "<div style='text-align:center;margin:20px 0px;'>";
	$page_count=ceil($row_count/ROW_PER_PAGE);
	if($page_count>1) {
		for($i=1;$i<=$page_count;$i++){
			if($i==$page){
				$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page current" />';
			} else {
				$per_page_html .= '<input type="submit" name="page" value="' . $i . '" class="btn-page" />';
			}
		}
	}
	$per_page_html .= "</div>";
}

$query = $sql.$limit;
$pdo_statement = $pdo_conn->prepare($query);
$pdo_statement->bindValue(':keyword', '%' . $search_keyword . '%', PDO::PARAM_STR);
$pdo_statement->execute();
$result = $pdo_statement->fetchAll();

View DemoDownload

Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js