PHP Pagination Tutorial

In this tutorial I will cover how to properly paginate mysql database results using php pagination. When I originally googled php pagination tutorial most of the tutorials I came across were incorrect, and their code did not work correctly. They either did not display the correct amount of pages, or the wrong data was being displayed on the wrong page, or even worse, query errors were coming up.

Writing basic pagination functionality is actually pretty easy. All you have to do is break it down into 3 steps: 1) find the total amount of pages to display, 2) the query to populate your results, and 3) showing the page numbers. To make this php pagination tutorial as simple as possible, we are going to break up some of the code into functions, which will make the code more maintainable and easier to re-use later on another project.

Demo

//paging.php

function count_results() {
  $link = mysql_connect('localhost', 'username', 'password');
  mysql_select_db('database')
  $result = mysql_query("SELECT id FROM `table`", $link);
  $total = mysql_num_rows($result);
  mysql_close($link);
  return $count;
}

function get_results($position, $items_per_page) {
  $link = mysql_connect('localhost', 'username', 'password');
  mysql_select_db('database')
  $result = mysql_query("SELECT * FROM `table` LIMIT $position, $items_per_page", $link);
  while($row = mysql_fetch_assoc($result)) {
  	$data[] = $row;
  }
  return $data;
}

//how many results you want to show on each page
$items_per_page = 15; 
//grab the total number of results
$total_results = count_results();
//divide the number of results by how many items to display per page to get the total page numbers
$total_pages   = ceil( $total_results / $items_per_page );

//make sure the given page number is valid, and subtract it by 1
//if you do not subtract by 1, then the wrong results will be displayed
if( isset( $_GET['page'] ) && is_numeric( $_GET['page'] ) {
    $page = $_GET['page'] - 1;
} else {
    $page = 0;
}
  //this is the offset used in the LIMIT query
  $position = $page * $items_per_page;

  //get the results
  $items = get_results( $position, $items_per_page );

  //display the results
  foreach( $items as $item ) {
     echo $item['column'] . '<br>';
  }

  //display the page numbers at the bottom
  $pagination = '';
  if( $total_pages > 1 ) {
     $pagination .= '<ul>';
     for( $i = 1; $i<=$total_pages; $i++ ) {
	if( $i == $page + 1 ) {
	   $pagination .= '<li><a href="paging.php?page=' . $i . '"><u>'.$i.'</u></a></li>';
	} else {
	   $pagination .= '<li><a href="paging.php?page=' . $i . '">'.$i.'</a></li>';	
	}	
     }
     $pagination .= '</ul>';
  }

  echo $pagination; //display the page numbers

And there you have it! A PHP Pagination Tutorial. If you have any questions, please leave a comment below and I will be sure to answer them.

Leave a Comment

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