PHP GuruPHP, MySQL, JavaScript, AJAX, MVC, Codeigniter
  • Technology
    • PHP
    • PHP Frameworks
    • MySql
    • Javascript
    • AJAX
    • React Js
    • Node JS
    • ChatGPT
  • JSON Viewer
  • XML Viewer

Pagination using PHP and MySQL – 2

Date January 3, 2014 Author By PravinS Category MySql, PHP

Below is simple pagination script using PHP and MySQL. Links will be in following format.
First | Previous | Next | Last

CSS class for pagination link.

	.paging
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#000000;
		text-decoration: none;
	}
	a.paging:link
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#000000;
		text-decoration: underline;
	}
	a.paging:visited 
	{
		FONT-SIZE: 11px; 
		COLOR: #000000; 
		font-family:Verdana, Arial, Helvetica, sans-serif;
		TEXT-DECORATION: underline;
	}
	a.paging:active 
	{
		FONT-SIZE: 11px; 
		COLOR: #000000; 
		font-family:Verdana, Arial, Helvetica, sans-serif;
		TEXT-DECORATION: underline;
	}
	a.paging:hover 
	{
		font-family:Verdana, Arial, Helvetica, sans-serif;
		font-size: 11px;
		color:#FF9900;
		text-decoration: none;
	}

Here are some required functions/methods which are used for Pagination.

	function execute($sql)
	{
		if ($sql!="")
		{
			$result = mysql_query($sql) or die("Error: ".mysql_errno().":- ".mysql_error());
			if ($result)
				return $result;
			else
				return false;
		}
	}

	function recordset($result)
	{
		if ($result)
		{
			while($row = mysql_fetch_assoc($result))
				$data[] = $row;
		}
		return $data;
	}

Pagination function

	function pagingPN($sql, $page, $limit, $getvars, $class)
	{
		if ($page == "")
			$page = 1;
		if ($limit == 0)
			$limit = 1;
		$tsql = $sql;
		$result = mysql_query($tsql) or die("Error: ".mysql_errno().":- ".mysql_error());
		$total = mysql_num_rows($result);
		$totnumpages = ceil($total/$limit);
		if ($offset < 0)
			$offset = 0;
		else          
			$offset = ($page - 1) * $limit;
		$sql = $sql. "  limit $offset, $limit"; 
		$rs = execute($sql);
		$res = recordset($rs);
		$serial_no = ($page - 1) * $limit;
		
		if ($total > 0)
		{
			$link .= "<font face='verdana' size='1'>Page: <strong>".$page."</strong>&nbsp;of&nbsp;<strong>".$totnumpages."</strong>&nbsp;&nbsp;&nbsp;Goto:&nbsp;</font>";
			
			if ($page > 1)
			{
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=1$getvars class='".$class."' title='Jump to First Page'>First</a>&nbsp;|&nbsp;";
				$prev = $page - 1;
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$prev."$getvars class='".$class."' title='Goto Previous Page'>Previous</a><span class='".$class."'>&nbsp;|&nbsp;</span>";
			}
			else
			{
				$link .= "<span class='".$class."' title='Jump to First Page'>First</span>&nbsp;|&nbsp;<span class='".$class."' title='Goto Previous Page'>Previous&nbsp;|&nbsp;</span>";
			}
			if ($page < $totnumpages)
			{
				$next = $page + 1;
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$next."$getvars class='".$class."' title='Goto Next Page'>Next</a>&nbsp;|&nbsp;";
				$link .= "<a href=".$_SERVER['PHP_SELF']."?page=".$totnumpages."$getvars class='".$class."' title='Jump to Last Page'>Last</a>";
			}
			else
			{
				$link .= "<span class='".$class."' title='Goto Next Page'>Next</span>&nbsp;| <span class='".$class."' title='Jump to Last Page'>Last</span>";
			}
		}		
		$retarr["sql"] = $sql;
		$retarr["records"] = $res;
		$retarr["serial_no"] = $_no;
		$retarr["link"] = $link;
		return $retarr;
	}

Usage of Pagination function
pagingPN() function will return result in array format with SQL query, record set, serial number and pagination link. So just use pagination function like this.

$sql = "SELECT QUERY";
$page = trim($_GET['page']);
$limit = 20; // records per page
$getvars = "&var1=val1&var2=val2"; // query string parameters
$class = "paging"; // pagination links CSS
$result_array = pagingPN($sql, trim($_GET['page']), $limit, $getvars, $class);
$pagination_link = $result_array['link']; // just echo this variable where you want to display the pagination links (First | Previous | Next | Last)
$serial_no = $result_array['serial_no']; // use this variable in while or for loop to display serial numbers of records (use as $serial_no++;)
$records = $result_array['records']; // result set will be in 2 dimensional array format
$sql = $result_array['sql']; // sql is for debugging purpose

May this will be help full !!!

Tags: pagination in php mysql, php mysql pagination, php pagination, php pagination function

2 thoughts on “Pagination using PHP and MySQL – 2”

  • Jamez says:
    February 2, 2018 at 8:44 pm

    Hi, phpguru.

    A few years ago I worked with a code that you developed … I don’t remember the project’s name.
    The only thing I remember is that when I write the table’s name in the url, the CRUD of the table appears.

    Could you share me this project, please?

    Reply
    • PravinS says:
      April 17, 2018 at 5:34 am

      sorry bro, I couldn’t able to find that script.

      Reply

Leave a Reply to PravinS Cancel reply

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

Recent Posts

  • Comparison between CodeIgniter and Laravel
  • SOAP Client In CodeIgniter using NuSOAP PHP Toolkit
  • Pagination using PHP and MySQL – 1
  • Pagination using PHP and MySQL – 2
  • Upload Files using PHP CURL
  • Search Value in Multidimensional Array Using PHP
  • SOAP Server In CodeIgniter using NuSOAP PHP Toolkit
  • Simple JavaScript/CSS Lightbox
  • Import CSV data to MySQL Using PHP
  • Export MySQL data to CSV file Using PHP

Pages

  • ChatGPT
  • Node JS
  • PHP
  • React Js

Tag Cloud

A-Z alphabets in php AJAX alphabets using php array search in php Asynchronous JavaScript and XML CI NUSOAP library Client Side codeigniter codeigniter nusoap library coparison chart Database file upload using curl html to pdf import csv to mysql Javascript javascript css lightbox laravel lightbox MySQL nusoap client in codeigniter nusoap in codeigniter nusoap integration with codeigniter Open Source pagination in php and mysql pagination in php mysql pasination using php pdf pdf generation in codeigniter PHP php and mysql pagination php curl file upload php file upload using curl php mysql pagination php pagination php pagination function RDBMS Scripting Language search in multidimensional array Server Side language soap client in codeigniter soap in codeigniter soap server in codeigniter soap server using nusoap in codeigniter SQL TCPDF with CodeIgniter

Categories

  • AJAX
  • Javascript
  • MySql
  • PHP
    • PHP Frameworks

Recent Posts

  • Comparison between CodeIgniter and Laravel
  • SOAP Client In CodeIgniter using NuSOAP PHP Toolkit
  • Pagination using PHP and MySQL – 1
  • Pagination using PHP and MySQL – 2
  • Upload Files using PHP CURL
  • Search Value in Multidimensional Array Using PHP
  • SOAP Server In CodeIgniter using NuSOAP PHP Toolkit
  • Simple JavaScript/CSS Lightbox
  • Import CSV data to MySQL Using PHP
  • Export MySQL data to CSV file Using PHP

Tag Cloud

A-Z alphabets in php AJAX alphabets using php array search in php Asynchronous JavaScript and XML CI NUSOAP library Client Side codeigniter codeigniter nusoap library coparison chart Database file upload using curl html to pdf import csv to mysql Javascript javascript css lightbox laravel lightbox MySQL nusoap client in codeigniter nusoap in codeigniter nusoap integration with codeigniter Open Source pagination in php and mysql pagination in php mysql pasination using php pdf pdf generation in codeigniter PHP php and mysql pagination php curl file upload php file upload using curl php mysql pagination php pagination php pagination function RDBMS Scripting Language search in multidimensional array Server Side language soap client in codeigniter soap in codeigniter soap server in codeigniter soap server using nusoap in codeigniter SQL TCPDF with CodeIgniter

Advertisements

Categories

  • AJAX
  • Javascript
  • MySql
  • PHP
    • PHP Frameworks
  • Theme created by PWT. Powered by WordPress.org

Copyright © 2014 PHP Guru