Page Numbers

Posted on 2004-10-11
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 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.

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

617 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