Entry
how can i show retrieve data in diff. pages showing only by ten every pages user click next
Apr 27th, 2005 05:09
Keyar Srinivasan, nelskie nelskie,
To show the result of a query in several pages first you need to know
how many rows you have and how many rows per page you want to show. For
example if I have 295 rows and I show 30 rows per page that mean I'll
have ten pages (rounded up).
For the example I created a table named randoms that store 295 random
numbers. Each page shows 20 numbers.
http://www.php-mysql-tutorial.com/examples/paging/paging.php
http://www.php-mysql-tutorial.com/examples/paging/paging3.php
http://www.php-mysql-tutorial.com/examples/paging/paging2.php
Here is the Code for this
<html>
<head>
<title>Implementing Paging with next and prev</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-
1">
</head>
<body>
<?php
include '../library/config.php';
include '../library/opendb.php';
// how many rows to show per page
$rowsPerPage = 20;
// by default we show first page
$pageNum = 1;
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$query = "SELECT val FROM randoms LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');
// print the random numbers
while($row = mysql_fetch_array($result))
{
echo $row['val'] . '<br>';
}
echo '<br>';
// how many rows we have in database
$query = "SELECT COUNT(val) AS numrows FROM randoms";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current
page
}
else
{
$nav .= " <a href=\"$self?page=$page\">$page</a> ";
}
}
// creating previous and next link
// plus the link to go straight to
// the first and last page
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Prev]</a> ";
$first = " <a href=\"$self?page=1\">[First Page]</a> ";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";
$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}
// print the navigation link
echo $first . $prev . $nav . $next . $last;
// and close the database connection
include '../library/closedb.php';
?>
</body>
</html>
This article is from the Page :
http://www.php-mysql-tutorial.com/php-mysql-paging.php
Please refer this for More Details needed
-------------------------------------------------------------------
Although there are several examples of this type of code to be found
online.
Plain Pagination Code:
<?php
/*
Description: This code will allow you to provide item listings
as x per page, and will generate the Pages: 1 2 3, etc links
if needed. You will need to modify the queries as per your
own actual needs, and the database connection/selection code is
not included here, so it's assuming that code is already somewhere else
in your page. (Thats why this code snippet page will give you an error
if it executes - no database info.)
Don't get freaked out by how long it is - 70% of it is comments to
help you understand what it is that we're doing. */
/* BEGIN CODE SECTION */
/* ----------------------------------------*/
/* per page limit - this can be included in a seperate file, as long as
you
are sure to include that fle on the page you want the numbering on -
otherwise
its fine to just code it here - for this example, we're using 16 items
per page */
$user_view_limit = "16";
/* if there is no page # passed, assign $page the value of 1 */
if ((empty($page)) || ($page <= 0)){
$page = 1;
}
/* this code just figures out the limit for the sql statement that
actually
gets that page's item data */
$limitvalue = $page*$user_view_limit-($user_view_limit);
/* the query to get actual results - your query would go here, but be
sure to
include the LIMIT $limitvalue, $user_view_limit part at the end.
Our example is pulling articles from the "articles" table that have the
category ID of 2 */
$sql = "select Title from articles where CatID=2 LIMIT $limitvalue,
$user_view_limit";
/* the query to get the total number without the limit */
$sqlcount = "select count(*) from articles where CatID=2 ";
/* this is used by the function in case you need to pass other stuff in
your
query string. If you're not passing anything else, this should be set
to just "?"
as is shown in this example -
To pass more variables through the query string, you would just change
it to
something like: $print_query ="?cat_id=$cat_id&"; */
$print_query ="?";
/* get the total number data and find out what the grand total is */
$sql_countresult = mysql_query($sqlcount);
list($totalrows) = mysql_fetch_row($sql_countresult);
/* get the actual item data and print it out on the page */
if ($get_items = mysql_query($sql)) {
$num_items = mysql_num_rows($get_items);
/* see if we actually have any matches in the DB */
if ($num_items > 0) {
/* if theres more than one page needed, print out the page #s
In this example, products.php is the page that the link will be printed
out with.
To use a different page, simply change this value in your function call
*/
if ($user_view_limit < $totalrows) {
make_user_page_nums($totalrows, $print_query, "$PHP_SELF");
}
/* print out the actual item details - you would cange this code to
make it print out the fields and data the way you want it to appear
on the page */
while (list($foo) = mysql_fetch_row($get_items) ) {
echo "<li>";
echo $foo;
}
/* if there are no matches, print our an error */
} else {
echo "No items listed";
}
/* if the query failed, lets see if mysql returns an error */
} else {
echo "An error has occurred: <br>";
echo mysql_error();
}
/* THE ACTUAL make_user_page_nums FUNCTION */
/* ----------------------------------------*/
function make_user_page_nums($totalrows, $print_query, $page_name) {
// global $print_query;
global $user_view_limit;
global $page;
global $limitvalue;
echo "Pages: ";
/* PREV LINK: print a Prev link, if the page number is not 1 */
if($page != 1) {
$pageprev = $page - 1;
echo "<a
href=\"".$page_name.$print_query."page=".$pageprev."\"><Prev</a> ";
}
/* get the total number of pages that are needed */
$numofpages = $totalrows/$user_view_limit;
/* loop through the page numbers and print them out */
for($i= 0; $i < $numofpages; $i++) {
/* if the page number in the loop is not the same as the page we're on,
make it a link */
$real_page = $i + 1;
if ($real_page!=$page){
echo " <a
href=\"".$page_name.$print_query."page=".$real_page."\">".$real_page."</
a> ";
/* otherwise, if the loop page number is the same as the page we're on,
do not make it
a link, but rather just print it out */
} else {
echo "<b>".$real_page."</b>";
}
}
/* NEXT LINK -
If the totalrows - $user_view_limit * $page is > 0 (meaning there is a
remainder), print the Next button. */
if(($totalrows-($user_view_limit*$page)) > 0){
$pagenext = $page + 1;
echo " <a href=\"".$page_name.$print_query."page=".$pagenext."\">Next
></a> ";
}
}
/* END OF PAGE NUMBERING SNIPPET CODE */
/* ----------------------------------------*/
?>
--------------------------------------------
Google-like Page Numbering Code:
<?php
/*
Description: This code will allow you to provide item listings
as x per page, and will generate the Pages: 1 2 3, etc links
if needed. You will need to modify the queries as per your
own actual needs, and the database connection/selection code is
not included here, so it's assuming that code is already somewhere else
in your page. (Thats why this code snippet page will give you an error
if it executes - no database info.)
Don't get freaked out by how long it is - 70% of it is comments to
help you understand what it is that we're doing. */
/* BEGIN CODE SECTION */
/* ----------------------------------------*/
/* per page limit - this can be included in a seperate file, as long as
you
are sure to include that fle on the page you want the numbering on -
otherwise
its fine to just code it here - for this example, we're using 16 items
per page */
$user_view_limit = "16";
/* set this variable to whatever the max number of page numbers you
wish to be
displayed at any given time */
$max_pages_to_show = 5;
/* if there is no page # passed, assign $page the value of 1 */
if ((empty($page)) || ($page <= 0)){
$page = 1;
}
/* this code just figures out the limit for the sql statement that
actually
gets that page's item data */
$limitvalue = $page*$user_view_limit-($user_view_limit);
/* the query to get actual results - your query would go here, but be
sure to
include the LIMIT $limitvalue, $user_view_limit part at the end.
Our example is pulling articles from the "articles" table that have the
category ID of 2 */
$sql = "select Title from articles where CatID=2 LIMIT $limitvalue,
$user_view_limit";
/* the query to get the total number without the limit */
$sqlcount = "select count(*) from articles where CatID=2 ";
/* this is used by the function in case you need to pass other stuff in
your
query string. If you're not passing anything else, this should be set
to just "?"
as is shown in this example -
To pass more variables through the query string, you would just change
it to
something like: $print_query ="?cat_id=$cat_id&"; */
$print_query ="?";
/* get the total number data and find out what the grand total is */
$sql_countresult = mysql_query($sqlcount);
list($totalrows) = mysql_fetch_row($sql_countresult);
/* get the actual item data and print it out on the page */
if ($get_items = mysql_query($sql)) {
$num_items = mysql_num_rows($get_items);
/* see if we actually have any matches in the DB */
if ($num_items > 0) {
/* if theres more than one page needed, print out the page #s
In this example, products.php is the page that the link will be printed
out with.
To use a different page, simply change this value in your function call
*/
if ($user_view_limit < $totalrows) {
make_user_page_nums($totalrows, $print_query, $_SERVER['PHP_SELF'],
$user_view_limit, $page, $max_pages_to_show);
}
/* print out the actual item details - you would cange this code to
make it print out the fields and data the way you want it to appear
on the page */
while (list($foo) = mysql_fetch_row($get_items) ) {
echo "<li>";
echo $foo;
}
/* if there are no matches, print our an error */
} else {
echo "No items listed";
}
/* if the query failed, lets see if mysql returns an error */
} else {
echo "An error has occurred: <br>";
echo mysql_error();
}
/* THE ACTUAL make_user_page_nums FUNCTION */
/* ----------------------------------------*/
function make_user_page_nums($total_results, $print_query, $page_name,
$results_per_page, $page, $max_pages_to_show) {
echo "Pages: ";
/* PREV LINK: print a Prev link, if the page number is not 1 */
if($page != 1) {
$pageprev = $page - 1;
echo "<a
href=\"".$page_name.$print_query."page=".$pageprev."\"><Prev</a> ";
}
/* get the total number of pages that are needed */
$showpages = round($max_pages_to_show/2);
$numofpages = $total_results/$results_per_page;
if ($numofpages > $showpages ) {
$startpage = $page - $showpages ;
} else {
$startpage = 0;
}
if ($startpage < 0){
$startpage = 0;
}
if ($numofpages > $showpages ) {
$endpage = $page + $showpages;
} else {
$endpage = $showpages;
}
if ($endpage > $numofpages){
$endpage = $numofpages;
}
/* loop through the page numbers and print them out */
for($i = $startpage; $i < $endpage; $i++) {
/* if the page number in the loop is not the same as the page were on,
make it a link */
$real_page = $i + 1;
if ($real_page!=$page){
echo " <a
href=\"".$page_name.$print_query."page=".$real_page."\">".$real_page."</
a> ";
/* otherwise, if the loop page number is the same as the page were on,
do not make it a link, but rather just print it out */
} else {
echo "<b>".$real_page."</b>";
}
}
/* NEXT LINK -If the totalrows - $results_per_page * $page is > 0
(meaning there is a remainder), print the Next button. */
if(($total_results-($results_per_page*$page)) > 0){
$pagenext = $page + 1;
echo " <a href=\"".$page_name.$print_query."page=".$pagenext."\">Next
></a> ";
}
}
/* END OF PAGE NUMBERING SNIPPET CODE */
/* ----------------------------------------*/
?>
