A few years ago, as I was staring my 'adventure' with php, I could not find any simple way of pagination the mysql results retuned by the query, I found it really frustrating to be honest.

There was a lot of stuff on out there but none of the solutions I found was clear enogh and I though it could be done in easier way skipping some of useless stuff.

Expressing the way pagination works is really simple and I think to most of you will be obvious:

1. Set max number of items per page 2. Select all the items from a database - to check how many pages will be required 3. Another query retrieving only the selected records according to max number of items per page, and the page (in turn) that is currently being visited.

//1

$max_results = 10;



if(!isset($_GET['page'])){ $page = 1; } else { $page = $_GET['page']; }

$from = (($page * $max_results) - $max_results);

//eg. on 4th page (having 120 record in DB) this will be (4X10)-10=30

(filer the result starting from row no. 30)



//2

$sql_get_all = "SELECT * FROM $table";

$result = mysqli_query($cxn,$sql_get_all)

or die("Could not execute query 1");



$totals = mysqli_num_rows($result);

$total_pgs = ceil($totals / $max_results);



//3

$sql_get_selected = "SELECT * FROM $table LIMIT $from, $max_results";



$result2 = mysqli_query($cxn,$sql2)

or die("Could not execute query 2");

Generating a navigation links between the pages is also very simple:

$paginator = "";



if($page > 1) {



$prev = ($page - 1);

$paginator ="<a href="".$_SERVER['PHP_SELF']."?page=$prev">

Previous page</a>";



}



for($i = 1; $i <= $total_pgs; $i++) {

if(($page) == $i) {

  $paginator .= "$i ";

  }

  else {

  $paginator .="<a href="".$_SERVER['PHP_SELF']."? page=$i">$i</a> ";

  }

}

if($page < $total_pgs){

  $next = ($pg + 1);

  $paginator .="<a href="".$_SERVER['PHP_SELF']."?page=$next">

Next page.</a>";

}



echo "$paginator";



...results...



echo "Viewing page $page of $total_pgs";

Tagged: php  arrays  web programming  script  php script  pagination  mysql  data presentation  database access