Pagination of Data using PHP-MySQL

Pagination of Data (Paging) using PHP (Hyper Text Pre Processor) and MySQL (the great database)

 

First: Create a table named user_result in your MySQL Server

 

CREATE TABLE user_result(            user_id INT(5) NOT NULL AUTO_INCREMENT,            user_name VARCHAR(30),            math_marks INT(2),            phy_marks INT(2) ,            PRIMARY KEY(user_id) );

 

Second: Insert some large amount of data into the Table. As without large amount of data you cannot see the full functionality of this script .l

So I think you must insert near about 50 rows to the table.

Thrid: Test this script

Create a file named pagination.php and put the below contents to that file. Upload that file to your local server or main server.

I have uploaded that on my local  server where php is running. If you are testing this script elsewhere then change http://localhost with your server host name.

<?php //REMEMBER TO CONNECT TO DATABASE! $link = mysql_connect(’server_name’,'user_name’,'password’)            or die(“Error connecting to Database: ” . mysql_error()); mysql_select_db(‘test_db’) or                die(“Error Selecting Database: ” . mysql_error()); //**EDIT TO YOUR TABLE NAME, ETC. $t = mysql_query(“SELECT count(*) FROM `user_result` as cnt’”);  if(!$t){        die(“Error quering the Database: ” . mysql_error()); }     $a = mysql_fetch_object($t); $total_items = $a->cnt; echo “Total Number of records in Database: “.$total_items; $limit= $_GET['limit']; $page= $_GET['page']; //Set default if: $limit is empty, non numerical, //less than 10, greater than 50 if((!$limit)  || (is_numeric($limit) == false)             || ($limit < 10) || ($limit > 50)) {      $limit = 10; //default } //Set default if: $page is empty, non numerical, //less than zero, greater than total available if((!$page) || (is_numeric($page) == false)          || ($page < 0) || ($page > $total_items)) {       $page = 1; //default } //calculate total pages $total_pages     = ceil($total_items / $limit); $set_limit          = $page * $limit – ($limit); //query: **EDIT TO YOUR TABLE NAME, ETC. $q = mysql_query(“SELECT * FROM `user_result`                                      LIMIT $set_limit, $limit”); if(!$q) die(mysql_error()); $no_row = mysql_num_rows($q); if($no_row == 0) die(“No matches met your criteria.”); //Results per page: **EDIT LINK PATH**  echo(“    <a href=http://localhost/pagination.php?limit=10&page=1>  10</a> |  <a href=http://localhost/pagination.php?limit=25&page=1>   25</a> |  <a href=http://localhost/pagination.php?limit=50&page=1>   50</a> “); //show data matching query: while($code = mysql_fetch_object($q)) {   //print your data here.    echo(“————————————–”);   echo(“Name: “.$code->user_name.”<BR/>”);      echo(“Math Marks: “.$code->math_marks.”<BR/>”);   echo(“Physics Marks: “.$code->phy_marks.”<BR/>”);   echo(“————————————–”); } //prev. page: **EDIT LINK PATH** $prev_page = $page – 1; if($prev_page >= 1) {   echo(“<b><<</b> <a href=http://localhost/pagination.php?limit=$limit&page=$prev_page> <b>Prev.</b></a>”); } //Display middle pages: **EDIT LINK PATH** for($a = 1; $a <= $total_pages; $a++) {    if($a == $page) {       echo(“<b> $a</b> | “); //no link      }   else {        echo(“         <a href=http://localhost/pagination.php?limit=$limit&page=$a> $a         </a> | “);      } } //Next page: **EDIT THIS LINK PATH** $next_page = $page + 1; if($next_page <= $total_pages) {    echo(“ <a  href=http://localhost/pagination.php?limit=$limit          &page=$next_page><b>Next</b></a> > >”); } //all done ?>

Four: Run this script in browser and see pagination with php and mysql in action.

Explanation: First this script will query the database and get total number of rows by the “SELECT count(*) FROM `user_result` as cnt’” query. It will return the count as cnt. Now get the count and store it in the valirable $total_items.

Now get the limit and page number from the GET variables. For the first time both will be null, so by default we will set the limit to 10 and page to 1.

If this variables are not blank, i.e. if the users click on the next or previous or page links then the page numeber and number of items to displayed will be not null and we will display data accordingly.

If some how this limit and page values are greater than the specified values or less than the specified values we will set them as default.

Now we will calculate how many pages the data will span. i.e. if we select 10 data to displayed at one time and total number of data are 85 then there will be 9 pages and by default the first page will be displayed. In this way if we choose to display 25 data at one time then the total number of pages will be 4, first 3 will display 25 data each and the last will display 10 data.

Hope this tutorial will help you to explre the vast world of pagination using php and mysql.

Kindly send me any queries that you might have.