This example demonstrates how data from a database table is displayed as different pages in a webpage.
In this example records per page is stored in a variable $pagesize and it is set to 15 (you can change it as per your requirements). For each page the query is selecting only the required 15 records and displays it.
In this example records per page is stored in a variable $pagesize and it is set to 15 (you can change it as per your requirements). For each page the query is selecting only the required 15 records and displays it.
Starting record of the given page ($pageno) is calculated by the following expression.
$start_rec = (($pageno-1) * $pagesize) ;
Query to select required number of records starting from start_rec is given below. To specify the starting record and number of records in SQL query the LIMIT clause is used.
$strSQL="SELECT * FROM tblfunctions "
. " LIMIT " . $start_rec . "," . $pagesize . ";";
. " LIMIT " . $start_rec . "," . $pagesize . ";";
Total number of pages is calculated by counting the number of records available in the table and dividing it by the page size.
ceil($rec_count/$pagesize)
Entire code of this example is given below:
pagination.php
<html>
<head>
<title>Page wise listing</title>
</head>
<body>
<?php
// --- Database Settings -----
$hostname = "localhost";
$user = "root";
$pwd = "xxxxxxx";
$database = "dbphptest";
// ---------------------------
// Inital page number is 1
$pageno = 1;
// If page number is passed through url
if (isset($_REQUEST['pageno']))
$pageno = $_REQUEST['pageno'];
// Records per page is set to 15
$pagesize = 15;
// Calculating the starting record number
$start_rec = (($pageno-1) * $pagesize) ;
// Connect to database server and selects the database
mysql_connect($hostname ,$user,$pwd);
mysql_select_db($database);
// Creating the query
$strSQL="SELECT * FROM tblfunctions "
. " LIMIT " . $start_rec . "," . $pagesize . ";";
// Executes the query
$result= mysql_query($strSQL);
// Header section of output
echo "<table align=center width=90%>";
echo "<tr bgcolor=#F1F3C3>";
echo "<td colspan=3>";
echo "<h1>Financial Function in Excel</h1>";
echo "</td>";
echo "</tr>";
echo "<tr bgcolor=#F1F3C3>";
echo "<th>Sl. No</th>";
echo "<th>Function</th>";
echo "<th>Description</th>";
echo "</tr>";
// Content of the table
$slno = $start_rec + 1;
while ($row = mysql_fetch_array($result)){
if ($slno % 2 == 0)
echo "<tr bgcolor=#F8F8F1>";
else
echo "<tr bgcolor=#ffffff>";
echo "<td>";
echo $slno;
echo "</td>";
echo "<td>";
echo $row['fn_name'];
echo "</td>";
echo "<td>";
echo $row['fn_desc'];
echo "</td>";
echo "</tr>";
$slno++;
}
// Gets the total number of records in the database table
$strSQL = "SELECT COUNT(*) AS rec_count FROM tblfunctions;";
$result = mysql_query($strSQL);
$rec_count = 0;
while ($row = mysql_fetch_array($result)){
$rec_count = $row['rec_count'];
}
mysql_close();
// Displays page numers as links
echo "<tr>";
echo "<td align=center colspan=3>[ ";
for ($i=1; $i<=ceil($rec_count/$pagesize); $i++) {
if ($i==$pageno)
echo "<b>" . $i . "</b> ";
else
echo "<a href='pagination.php?pageno=" . $i ."'>" . $i . "</a> ";
}
echo "]</td>";
echo "<tr>";
echo "</table>";
?>
</body>
</html>
<head>
<title>Page wise listing</title>
</head>
<body>
<?php
// --- Database Settings -----
$hostname = "localhost";
$user = "root";
$pwd = "xxxxxxx";
$database = "dbphptest";
// ---------------------------
// Inital page number is 1
$pageno = 1;
// If page number is passed through url
if (isset($_REQUEST['pageno']))
$pageno = $_REQUEST['pageno'];
// Records per page is set to 15
$pagesize = 15;
// Calculating the starting record number
$start_rec = (($pageno-1) * $pagesize) ;
// Connect to database server and selects the database
mysql_connect($hostname ,$user,$pwd);
mysql_select_db($database);
// Creating the query
$strSQL="SELECT * FROM tblfunctions "
. " LIMIT " . $start_rec . "," . $pagesize . ";";
// Executes the query
$result= mysql_query($strSQL);
// Header section of output
echo "<table align=center width=90%>";
echo "<tr bgcolor=#F1F3C3>";
echo "<td colspan=3>";
echo "<h1>Financial Function in Excel</h1>";
echo "</td>";
echo "</tr>";
echo "<tr bgcolor=#F1F3C3>";
echo "<th>Sl. No</th>";
echo "<th>Function</th>";
echo "<th>Description</th>";
echo "</tr>";
// Content of the table
$slno = $start_rec + 1;
while ($row = mysql_fetch_array($result)){
if ($slno % 2 == 0)
echo "<tr bgcolor=#F8F8F1>";
else
echo "<tr bgcolor=#ffffff>";
echo "<td>";
echo $slno;
echo "</td>";
echo "<td>";
echo $row['fn_name'];
echo "</td>";
echo "<td>";
echo $row['fn_desc'];
echo "</td>";
echo "</tr>";
$slno++;
}
// Gets the total number of records in the database table
$strSQL = "SELECT COUNT(*) AS rec_count FROM tblfunctions;";
$result = mysql_query($strSQL);
$rec_count = 0;
while ($row = mysql_fetch_array($result)){
$rec_count = $row['rec_count'];
}
mysql_close();
// Displays page numers as links
echo "<tr>";
echo "<td align=center colspan=3>[ ";
for ($i=1; $i<=ceil($rec_count/$pagesize); $i++) {
if ($i==$pageno)
echo "<b>" . $i . "</b> ";
else
echo "<a href='pagination.php?pageno=" . $i ."'>" . $i . "</a> ";
}
echo "]</td>";
echo "<tr>";
echo "</table>";
?>
</body>
</html>
Table used in this example contains the function name and description of some functions from Microsoft Excel. It is listed in a HTML table as 15 records in a page.
Following is the structure and data of the table tblfunctions. It can be imported to MySQL database.
tblfunctions.sql
-- phpMyAdmin SQL Dump
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 10, 2014 at 09:24 AM
-- Server version: 5.5.27
-- PHP Version: 5.4.7
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `dbphptest`
--
-- --------------------------------------------------------
--
-- Table structure for table `tblfunctions`
--
CREATE TABLE IF NOT EXISTS `tblfunctions` (
`fn_name` varchar(10) DEFAULT NULL,
`fn_desc` varchar(142) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `tblfunctions`
--
INSERT INTO `tblfunctions` (`fn_name`, `fn_desc`) VALUES
('ACCRINT', 'Returns the accrued interest for a security that pays periodic interest'),
('ACCRINTM', 'Returns the accrued interest for a security that pays interest at maturity'),
('AMORDEGRC', 'Returns the depreciation for each accounting period by using a depreciation coefficient'),
('AMORLINC', 'Returns the depreciation for each accounting period'),
('COUPDAYBS', 'Returns the number of days from the beginning of the coupon period to the settlement date'),
('COUPDAYS', 'Returns the number of days in the coupon period that contains the settlement date'),
('COUPDAYSNC', 'Returns the number of days from the settlement date to the next coupon date'),
('COUPNCD', 'Returns the next coupon date after the settlement date'),
('COUPNUM', 'Returns the number of coupons payable between the settlement date and maturity date'),
('COUPPCD', 'Returns the previous coupon date before the settlement date'),
('CUMIPMT', 'Returns the cumulative interest paid between two periods'),
('CUMPRINC', 'Returns the cumulative principal paid on a loan between two periods'),
('DB', 'Returns the depreciation of an asset for a specified period by using the fixed-declining balance method'),
('DDB', 'Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify'),
('DISC', 'Returns the discount rate for a security'),
('DOLLARDE', 'Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number'),
('DOLLARFR', 'Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction'),
('DURATION', 'Returns the annual duration of a security with periodic interest payments'),
('EFFECT', 'Returns the effective annual interest rate'),
('FV', 'Returns the future value of an investment'),
('FVSCHEDULE', 'Returns the future value of an initial principal after applying a series of compound interest rates'),
('INTRATE', 'Returns the interest rate for a fully invested security'),
('IPMT', 'Returns the interest payment for an investment for a given period'),
('IRR', 'Returns the internal rate of return for a series of cash flows'),
('ISPMT', 'Calculates the interest paid during a specific period of an investment'),
('MDURATION', 'Returns the Macauley modified duration for a security with an assumed par value of $100'),
('MIRR', 'Returns the internal rate of return where positive and negative cash flows are financed at different rates'),
('NOMINAL', 'Returns the annual nominal interest rate'),
('NPER', 'Returns the number of periods for an investment'),
('NPV', 'Returns the net present value of an investment based on a series of periodic cash flows and a discount rate'),
('ODDFPRICE', 'Returns the price per $100 face value of a security with an odd first period'),
('ODDFYIELD', 'Returns the yield of a security with an odd first period'),
('ODDLPRICE', 'Returns the price per $100 face value of a security with an odd last period'),
('ODDLYIELD', 'Returns the yield of a security with an odd last period'),
('PMT', 'Returns the periodic payment for an annuity'),
('PPMT', 'Returns the payment on the principal for an investment for a given period'),
('PRICE', 'Returns the price per $100 face value of a security that pays periodic interest'),
('PRICEDISC', 'Returns the price per $100 face value of a discounted security'),
('PRICEMAT', 'Returns the price per $100 face value of a security that pays interest at maturity'),
('PV', 'Returns the present value of an investment'),
('RATE', 'Returns the interest rate per period of an annuity'),
('RECEIVED', 'Returns the amount received at maturity for a fully invested security'),
('SLN', 'Returns the straight-line depreciation of an asset for one period'),
('SYD', 'Returns the sum-of-years'' digits depreciation of an asset for a specified period'),
('TBILLEQ', 'Returns the bond-equivalent yield for a Treasury bill'),
('TBILLPRICE', 'Returns the price per $100 face value for a Treasury bill'),
('TBILLYIELD', 'Returns the yield for a Treasury bill'),
('VDB', 'Returns the depreciation of an asset for a specified or partial period by using a declining balance method'),
('XIRR', 'Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic'),
('XNPV', 'Returns the net present value for a schedule of cash flows that is not necessarily periodic'),
('YIELD', 'Returns the yield on a security that pays periodic interest'),
('YIELDDISC', 'Returns the annual yield for a discounted security; for example, a Treasury bill'),
('YIELDMAT', 'Returns the annual yield of a security that pays interest at maturity');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- version 3.5.2.2
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Nov 10, 2014 at 09:24 AM
-- Server version: 5.5.27
-- PHP Version: 5.4.7
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `dbphptest`
--
-- --------------------------------------------------------
--
-- Table structure for table `tblfunctions`
--
CREATE TABLE IF NOT EXISTS `tblfunctions` (
`fn_name` varchar(10) DEFAULT NULL,
`fn_desc` varchar(142) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `tblfunctions`
--
INSERT INTO `tblfunctions` (`fn_name`, `fn_desc`) VALUES
('ACCRINT', 'Returns the accrued interest for a security that pays periodic interest'),
('ACCRINTM', 'Returns the accrued interest for a security that pays interest at maturity'),
('AMORDEGRC', 'Returns the depreciation for each accounting period by using a depreciation coefficient'),
('AMORLINC', 'Returns the depreciation for each accounting period'),
('COUPDAYBS', 'Returns the number of days from the beginning of the coupon period to the settlement date'),
('COUPDAYS', 'Returns the number of days in the coupon period that contains the settlement date'),
('COUPDAYSNC', 'Returns the number of days from the settlement date to the next coupon date'),
('COUPNCD', 'Returns the next coupon date after the settlement date'),
('COUPNUM', 'Returns the number of coupons payable between the settlement date and maturity date'),
('COUPPCD', 'Returns the previous coupon date before the settlement date'),
('CUMIPMT', 'Returns the cumulative interest paid between two periods'),
('CUMPRINC', 'Returns the cumulative principal paid on a loan between two periods'),
('DB', 'Returns the depreciation of an asset for a specified period by using the fixed-declining balance method'),
('DDB', 'Returns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify'),
('DISC', 'Returns the discount rate for a security'),
('DOLLARDE', 'Converts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number'),
('DOLLARFR', 'Converts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction'),
('DURATION', 'Returns the annual duration of a security with periodic interest payments'),
('EFFECT', 'Returns the effective annual interest rate'),
('FV', 'Returns the future value of an investment'),
('FVSCHEDULE', 'Returns the future value of an initial principal after applying a series of compound interest rates'),
('INTRATE', 'Returns the interest rate for a fully invested security'),
('IPMT', 'Returns the interest payment for an investment for a given period'),
('IRR', 'Returns the internal rate of return for a series of cash flows'),
('ISPMT', 'Calculates the interest paid during a specific period of an investment'),
('MDURATION', 'Returns the Macauley modified duration for a security with an assumed par value of $100'),
('MIRR', 'Returns the internal rate of return where positive and negative cash flows are financed at different rates'),
('NOMINAL', 'Returns the annual nominal interest rate'),
('NPER', 'Returns the number of periods for an investment'),
('NPV', 'Returns the net present value of an investment based on a series of periodic cash flows and a discount rate'),
('ODDFPRICE', 'Returns the price per $100 face value of a security with an odd first period'),
('ODDFYIELD', 'Returns the yield of a security with an odd first period'),
('ODDLPRICE', 'Returns the price per $100 face value of a security with an odd last period'),
('ODDLYIELD', 'Returns the yield of a security with an odd last period'),
('PMT', 'Returns the periodic payment for an annuity'),
('PPMT', 'Returns the payment on the principal for an investment for a given period'),
('PRICE', 'Returns the price per $100 face value of a security that pays periodic interest'),
('PRICEDISC', 'Returns the price per $100 face value of a discounted security'),
('PRICEMAT', 'Returns the price per $100 face value of a security that pays interest at maturity'),
('PV', 'Returns the present value of an investment'),
('RATE', 'Returns the interest rate per period of an annuity'),
('RECEIVED', 'Returns the amount received at maturity for a fully invested security'),
('SLN', 'Returns the straight-line depreciation of an asset for one period'),
('SYD', 'Returns the sum-of-years'' digits depreciation of an asset for a specified period'),
('TBILLEQ', 'Returns the bond-equivalent yield for a Treasury bill'),
('TBILLPRICE', 'Returns the price per $100 face value for a Treasury bill'),
('TBILLYIELD', 'Returns the yield for a Treasury bill'),
('VDB', 'Returns the depreciation of an asset for a specified or partial period by using a declining balance method'),
('XIRR', 'Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic'),
('XNPV', 'Returns the net present value for a schedule of cash flows that is not necessarily periodic'),
('YIELD', 'Returns the yield on a security that pays periodic interest'),
('YIELDDISC', 'Returns the annual yield for a discounted security; for example, a Treasury bill'),
('YIELDMAT', 'Returns the annual yield of a security that pays interest at maturity');
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
Output
No comments:
Post a Comment