Go Premium for a chance to win a PS4. Enter to Win


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
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

927 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