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

Screenshot - PHP Search and Pagination using PDO

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

This PHP code tutorial was published on February 3, 2017.

↑ Back to Top