Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Page Numbers

Posted on 2004-10-11
Medium Priority
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
Question by:trent21
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 49

Expert Comment

ID: 12275546
First you would define some navigational variables:
$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:

$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:
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.

LVL 12

Expert Comment

ID: 12276065

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

$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;
LVL 32

Accepted Solution

ldbkutty earned 375 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.

Expert Comment

ID: 12278874
if (empty($start)) {
//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
              $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 now contains int of pages needed unless there is a remainder from division
              if ($num%$pageLimit) {
              // has remainder so add one page
            // check to see if last page
              if (!((($start+$pageLimit)/$pageLimit)===$pages) && $pages!=1) {
              // not last page so give NEXT link
              $nextLink = "&nbsp;<a href=\"$PHP_SELF?start=$news&q=$var\">Next 10 &gt;&gt;</a>";
                  $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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

722 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