Solved

Page Numbers

Posted on 2004-10-11
4
204 Views
Last Modified: 2008-03-06
Hey guys,
I have a page that fetches downloads from the SQL Database then lists them, my code for this is

$query_results = mysql_query('SELECT `hits`, `downloadid`, `downloadtext` FROM `downloads` ORDER BY `hits` DESC');

while($row = mysql_fetch_assoc($query_results))
  {
$dhits = getdownloads($row['downloadid']);
$dtext = $row['downloadtext'];
$durl = "download.php?did=" . $row['downloadid'];
eval("\$dtext = \"$dtext\";");
print $dtext;

  }
  echo '</table>';
}
}

Witch works fine in displaying the downloads, what i want is below the downloads a page number bar like 1,2,3,4 etc... I want each page two display 15 downloads, i have a basic idea of how to do this but some clean simple code would be nice. Thanks
0
Comment
Question by:trent21
4 Comments
 
LVL 49

Expert Comment

by:Roonaan
ID: 12275546
First you would define some navigational variables:
<?php
$pagesize = 15; //number of items on each page
$pageoffset = isset($_GET['offset']) ? intval($_GET['offset']) : 0; //first item on the page
?>

Then you would have 2 queries instead of one:

<?php
$number_of_items = 0;
$number_of_items_query = 'SELECT count(*) as `c` From `downloads`;

$q = mysql_query($number_of_items_query);
if(!mysql_error()) {$row = mysql_fetch_assoc($q); $number_of_items = intval($row[$c]);}

$get_items_quest = 'SELECT `hits`, `downloadid`, `downloadtext` FROM `downloads` ORDER BY `hits` DESC LIMIT '.$pageoffset.','.$pagesize;

$query_results = mysql_query($get_items_query);

/* your orignal code below */
while($row = mysql_fetch_assoc($query_results))
  {
$dhits = getdownloads($row['downloadid']);
$dtext = $row['downloadtext'];
$durl = "download.php?did=" . $row['downloadid'];
eval("\$dtext = \"$dtext\";");
print $dtext;
  }
/* end of your code */
?>

Then offcourse we have to add page numbers:
<?php
for($i = 0; $i < $number_of_items; $i += $pagesize)
{
  $pagenumber = floor($i / $pagesize) + 1;
  echo '<a href="'.$_SERVER['PHP_SELF'].'?offset='.$i.'">'.$pagenumber.'</a>';
}
?>

This can be somewhat shakey, because I wrote it from heart. But the idea should be clear i guess.

-r-
0
 
LVL 12

Expert Comment

by:minichicken
ID: 12276065
Hi

I have a function that I wrote myself that returns result in an array with the query results, record count, page navigation etc....
The array looks something like this:
Array ( [row_start] => 0 [rows_total] => 12 [rows_result] => 10 [result] => Resource id #7 [page_row_start] => 1 [page_row_end] => 10 [page] => 1 [pages] => 2 [page_nav] =>   1  2  ## )

row_start: row number starting on the current page
rows_total: total number of rows of the whole query
rows_result: number of rows of the current page
result: resource cursor of query result
page_row_start: the starting row of the current page
page_row_end: the ending row of the current page
page: current page number
pages: total number of pages
page_nav: the page navigation string

Hope you find it userful

************************************************************************************
//Example:
$query = "select * from mytable";
$my_result = db_result ($query, 15); //15 per page
print_r ($my_result); //prints the above array
echo $my_result['page_nav']; //output the page navigation string with html links

while ($row = mysql_fetch_assoc($my_result['$result']))
{
      echo $row['mycolumn']; //output all rows for that page
}

************************************************************************************************
<?
//get db result
      function db_result($query, $per_page)
      {
            $result_total = mysql_query($query);
            $rows_total = mysql_num_rows($result_total);
            
            /************************************************************************/
            //if $per_page is 0 then it indicates no paging is required
            if ($per_page == 0)
            {
                  $db_result = array('row_start' => 0, //SQL row start
                                             'rows_total' => $rows_total, //total number of rows of query
                                             'rows_result' => $rows_total, //total number of rows per page of query
                                             'result' => $result_total, //SQL results of query
                                             'page_row_start' => 1,//the row number starting the page
                                                'page_row_end' => $rows_total,//the row number ending the page
                                             'page' => 1, //current page
                                             'pages' => 1, //total number of pages
                                             'page_nav' => "" //page navigation string
                                                );
                  return $db_result;
            }
            /************************************************************************/
            //the code below is where $per_page > 0 which mean paging is required
            
            $page = (isset($_GET['page'])) ? $_GET['page'] : 1;//current page
            $pages = ceil($rows_total/$per_page); //work out number of pages
            
            $row_start = ($page > 1) ? $per_page * ($page - 1) : 0; //set row to start
            $result_page = mysql_query($query . " limit $row_start, $per_page"); //get result of page
            $rows_page = mysql_num_rows($result_page); //get rows of page
            
            //begin page nav
            
            //BEGIN: Exclude last qstring value
            $qstring_arr = explode("&",$_SERVER['QUERY_STRING']);
            
            if (substr($qstring_arr[count($qstring_arr) -1], 0, 4) == "page")
            {
                  array_pop ($qstring_arr);
            }
            $qstring = implode("&", $qstring_arr);
            //END: Exclude last qstring value
            
            $page_nav = ""; //navigation string
            
            if ($page > 1)
            {
                  $page_nav .= "&nbsp;&nbsp;<a href = '?$qstring&page=" . ($page - 1) . "' class= 'b' title = 'Previous page'>##</a>";
            }
                        
            for($i = 1; $i <= $pages; $i++) //generate page navigation
            {
                  $page_nav .= ($page == $i || ($page == "" && $i == 1)) ? "&nbsp;&nbsp;<a href ='?$qstring&page=$i' class='b'><b>$i</b></a>" : "&nbsp;&nbsp;<a href ='?$qstring&page=$i' class = 'b'>$i</a>";
            }
            
            if ($page < $pages)
            {
                  $page_nav .= "&nbsp;&nbsp;<a href ='?$qstring&page=" . ($page + 1). "' class = 'b' title = 'Next page'>##</a> ";
            }
            //end page nav
            
            
            $db_result = array('row_start' => $row_start, //SQL row start
                                       'rows_total' => $rows_total, //total number of rows of query
                                       'rows_result' => $rows_page, //total number of rows per page of query
                                       'result' => $result_page, //SQL results of query
                                       'page_row_start' => ($row_start + 1),//the row number starting the page
                                       'page_row_end' => ($row_start + $rows_page),//the row number ending the page
                                       'page' => $page, //current page
                                     'pages' => $pages, //total number of pages
                                       'page_nav' => $page_nav //page navigation string
                                       );
                                    
            return $db_result;
      }
?>
0
 
LVL 32

Accepted Solution

by:
ldbkutty earned 125 total points
ID: 12276715
There are lots of codes available for Paging. But, i personally feel that this helper class is one of the best and easier.
http://www.phpnoise.com/tutorials/9/3
0
 
LVL 2

Expert Comment

by:JGoyer
ID: 12278874
if (empty($start)) {
      $start=0;
      $pageLimit=15;
}
//page number
$currPage = (($start/$pageLimit) + 1);


  //query database for data
  $result = mysql_query("select fields from table   limit $start, $pageLimit");
 
//load up your data
//setup the pagenumbering

            if ($start>=1) { // bypass PREV link if start is 0
              $prevs=($start-$pageLimit);
              $prevLink = "&nbsp;<a href=\"$PHP_SELF?start=$prevs&q=$var\">&lt;&lt;
              Prev 10</a>&nbsp&nbsp;";
              }
            else $prevLink = '';
            
            // calculate number of pages needing links
              $pages=intval($num/$pageLimit);
            
            // $pages now contains int of pages needed unless there is a remainder from division
            
              if ($num%$pageLimit) {
              // has remainder so add one page
              $pages++;
              }
            
            // check to see if last page
              if (!((($start+$pageLimit)/$pageLimit)===$pages) && $pages!=1) {
             
              // not last page so give NEXT link
              $news=$start+$pageLimit;
            
              $nextLink = "&nbsp;<a href=\"$PHP_SELF?start=$news&q=$var\">Next 10 &gt;&gt;</a>";
              }
              else
                  $nextLink = '';
            
            $a = $start + ($pageLimit) ;
              if ($a > $num) { $a = $num ; }
              $b = $start + 1 ;
              $showing =  "<p>Showing results $b to $a of $num</p>";


Display these at the bottom: $nextLink, $prevLink, $showing
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now