trent21
asked on
Page Numbers
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_r esults))
{
$dhits = getdownloads($row['downloa did']);
$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
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_r
{
$dhits = getdownloads($row['downloa
$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
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_resu lt['$resul t']))
{
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_tot al);
/************************* ********** ********** ********** ********** *******/
//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_pag e); //get rows of page
//begin page nav
//BEGIN: Exclude last qstring value
$qstring_arr = explode("&",$_SERVER['QUER Y_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 .= " <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)) ? " <a href ='?$qstring&page=$i' class='b'><b>$i</b></a>" : " <a href ='?$qstring&page=$i' class = 'b'>$i</a>";
}
if ($page < $pages)
{
$page_nav .= " <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;
}
?>
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_resu
{
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_tot
/*************************
//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
$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_pag
//begin page nav
//BEGIN: Exclude last qstring value
$qstring_arr = explode("&",$_SERVER['QUER
if (substr($qstring_arr[count
{
array_pop ($qstring_arr);
}
$qstring = implode("&", $qstring_arr);
//END: Exclude last qstring value
$page_nav = ""; //navigation string
if ($page > 1)
{
$page_nav .= " <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)) ? " <a href ='?$qstring&page=$i' class='b'><b>$i</b></a>" : " <a href ='?$qstring&page=$i' class = 'b'>$i</a>";
}
if ($page < $pages)
{
$page_nav .= " <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;
}
?>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 = " <a href=\"$PHP_SELF?start=$pr evs&q=$var \"><< ;
Prev 10</a>  ";
}
else $prevLink = '';
// calculate number of pages needing links
$pages=intval($num/$pageLi mit);
// $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)/$p ageLimit)= ==$pages) && $pages!=1) {
// not last page so give NEXT link
$news=$start+$pageLimit;
$nextLink = " <a href=\"$PHP_SELF?start=$ne ws&q=$var\ ">Next 10 >></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
$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 = " <a href=\"$PHP_SELF?start=$pr
Prev 10</a>  ";
}
else $prevLink = '';
// calculate number of pages needing links
$pages=intval($num/$pageLi
// $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)/$p
// not last page so give NEXT link
$news=$start+$pageLimit;
$nextLink = " <a href=\"$PHP_SELF?start=$ne
}
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
<?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_ite
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.','.$pagesiz
$query_results = mysql_query($get_items_que
/* your orignal code below */
while($row = mysql_fetch_assoc($query_r
{
$dhits = getdownloads($row['downloa
$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
}
?>
This can be somewhat shakey, because I wrote it from heart. But the idea should be clear i guess.
-r-