When programming PHP network database, it is inevitable to consider the display of database record results. In order to present a beautiful page and speed up the page loading speed, the database records need to be displayed in pages.
Now I will share with you an example of paging display of mysql database records that is perfectly integrated with PHP.
The mysql database is xinxiku and the data table is joke. Its definition SQL statement is as follows:
create table joke (
id int(5) not null auto_increment,
biaoti varchar(40) not null,
neirong text not null,
primary key (id)
);
Field description:
id: record number, automatically incremented and the primary key
biaoti: title
neirong: content
<html>
<head>
<title>Implementation method of paging display</title>
<meta http-equiv=”content-type” content=”text/html; charset=gb2312″>
<script language=”javascript”>
/ * Define a pop-up window to display specific content*/
function popwin(url)
{
window.open(url,"","left=340, top=190, height=280, width=400, resizable=yes, scrollbars= yes, status=no, toolbar=no, menubar=no, location=no”);
}
</script>
</head>
<body leftmargin=0 topmargin=0 rightmargin=0 >
<?php
//Connect to database
$db =mysql_connect("localhost","root","");
mysql_select_db("xinxiku",$db);
//Set the number of records displayed on each page
$pagesize=15;
//Get the total number of records and calculate the total pages Use
$res=mysql_query(”select count(*) from joke ”,$db);
$myrow = mysql_fetch_array($res);
$numrows=$myrow[0];
//Calculate the total number of pages
$pages=intval( $numrows/$pagesize);
if ($numrows%$pagesize)
$pages++;
//Determine whether the page number is set or not. If not, define it as the homepage
if (!isset($page))
$page=1;
//Determine Go to page number
if (isset($ys))
if ($ys>$pages)
$page=$pages;
else
$page=$ys;
//Calculate record offset
$offset=$pagesize*($page- 1);
//Get records
$res=mysql_query("select id,biaoti from joke order by id desc limit $offset,$pagesize" ,$db);
//Loop display records
if ($myrow = mysql_fetch_array($res) )
{
$i=0;
?>
<table width=”101%” border=”0″ cellspacing=”0″ cellpadding=”0″>
<tr>
<td width=”5%” bgcolor=”#e1e9fb” ></td>
<td width=”95%” bgcolor=”#e1e9fb”><font color=”#ff6666″ size=”2″>Content</font></td>
</tr>
<?php
do {
$i++;
?>
<tr>
<td width=”5%” bgcolor=”#e6f2ff”><?php echo $i;?></td>
<td width=”95%” bgcolor=”# e6f2ff”><font size="2″>
<a href="javascript:popwin('jokenr.php?id=<?php echo $myrow[0];?>')" ><?php echo $myrow[ 1];?></a></font></td>
</tr>
<?php
}
while ($myrow = mysql_fetch_array($res));
echo “</table>” ;
}
//Display the total Page number
echo "<div align='center'>There are ".$pages." pages (".$page."/".$pages.")<br>";
//Display the number of pages
for ($i =1;$i<$page;$i++)
echo “<a href='fy.php?page=”.$i.”’>Page “.$i .”</a> “;
echo “Page ".$page."page";
for ($i=$page+1;$i<=$pages;$i++)
echo "<a href='fy.php?page=".$i."'> Page ".$i."</a> ";
echo "<br>";
//Display the page number
echo "<form action='fy.php' method='post'> ";
//Calculate Page values of home page, previous page, next page and last page
$first=1;
$prev=$page-1;
$next=$page+1;
$last=$pages;
if ($page>1)
{
echo “<a href='fy.php?page=”.$first.”’>Homepage</a> “;
echo “<a href=’fy.php?page=”.$prev.”’>Prev. page</a> ";
}
if ($page<$pages)
{
echo "<a href='fy.php?page=".$next."'>Next page</a> ";
echo " <a href='fy.php?page=”.$last.”'>Last page</a> “;
}
echo “Go to <input type=text name=’ys’ size=’2′ value=” .$page.">Page";
echo "<input type=submit name='submit' value='go'>";
echo "</form>";
echo "</div>";
?>
</body >
</html>
Previous page 1 23 Next page
program description:
1. Variable description
Variable $page: stores the current page number to be displayed.
Variable $pages: stores the total number of pages.
Variable $offset: stores the offset of the current page number.
Variable $pagesize: stores the number of records displayed on each page.
2. MySQL statement description
select id,biaoti from joke order by id desc limit $offset,$pagesize
The limit clause is used to limit the number of rows returned by the select statement. The first parameter in limit specifies the offset of the first row to be returned, and the second parameter specifies the maximum number of rows to be returned, pagesize.
Implementation principle:
Pass the page number variable page in the program, calculate the initial record position offset based on the passed page value when fetching records, and then obtain the record set based on the number of records required to be displayed in each page pagesize. Then display it.
Program implementation function:
The program provides two methods of page display (assuming that the current page is the third page).
1. Display all page numbers and provide links according to the number of pages. No links are provided for the current page. This is suitable when the number of pages is not too large. The style is shown in Figure 1:
/ShowImg.asp?p=/2006-3-29/19430aimagea1.jpg
2. Provide the total number of pages, and turn pages through the provided home page, previous page, next page, last page and jump to. Of course, if the current page is every page, links to the home page and previous page will not be provided. If the current page is the last page, links to the next page and last page will not be provided.
Previous page 1 2 3