PHP Search and Pagination using PDO

by Vincy. Last modified on July 10th, 2022.

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.

Screenshot - PHP Search and Pagination using PDO

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

Comments to “PHP Search and Pagination using PDO”

Leave a Reply

Your email address will not be published. Required fields are marked *

↑ Back to Top

Share this page