PHP PAGINATION output

output form

[Dragosh]

Hello. I still cannot understand how pagination works on google search or other forums. here is the code i thound for pagination :

<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("facts") or die(mysql_error());

//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * FROM content") or die(mysql_error());
$rows = mysql_num_rows($data);

//This is the number of results displayed per page
$limit = 4;

//This tells us the page number of our last page
$last = ceil($rows/$limit);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $limit .',' .$limit;
//This is your query again, the same one... the only difference is we add $max into it
$result = mysql_query("SELECT * FROM content $max") or die(mysql_error());

//This is where you display your query results
while($row = mysql_fetch_array( $result ))
{
Print $row['facts'];
echo "<br>";
}
echo "<p>";

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}

//just a spacer
echo " ---- ";

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}
?>

It works great but it doesnt show the normal page links.
All i want is to have something like this :prev¦ 1 ¦ 2 ¦ 3 ¦next . but if the page has for example 10 pages and i'm seeing page 5 to look like this :
prev¦ 1 ¦ 2 ¦ . . . ¦ 5 ¦ 6 ¦ . . . ¦ 9 ¦ 10 ¦ next .
Please, please help me do it.
the only idea i have are that three points are to be used when there are >=5 pages.

[justgowithit]

The script your using relies on register_globals. If you're going to keep using it I'd switch off globals and add:

$pagenum = $_GET['pagenum'];

before the first IF statement.

For what it's worth - here's a little script that I've tweaked to be a pretty simple but complete pagination script.

<?php

//-> Just looks for selected values in the form below
function show_limit($num) {

if (isset($_POST['lmt']) && $_POST['lmt'] == $num) {
$selected = ' selected="selected "';
}

if (isset($_GET['lmt']) && $_GET['lmt'] == $num) {
$selected = ' selected="selected "';
}

if (isset($selected)) {
echo $selected;
}

}

if (isset($_POST['lmt'])) {
$limit = $_POST['lmt'];
} elseif (isset($_GET['lmt'])) {
$limit = $_GET['lmt'];
} else {
$limit = 10;
}

$q = "SELECT * FROM table";
$r = mysql_query($q, GetMyConnection());
$totalrows = mysql_num_rows($r);

if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

$limitvalue = $page * $limit - ($limit);

$q = "SELECT * FROM table LIMIT $limitvalue, $limit";
$r = mysql_query($q) or die(mysql_error());

if (mysql_num_rows($r) > 0) {

?>
<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="POST">
Show <select name="lmt">
<option value="10"<?php show_limit (10);?>>10</option>
<option value="25"<?php show_limit (25);?>>25</option>
<option value="50"<?php show_limit (50);?>>50</option>
<option value="100"<?php show_limit (100);?>>100</option>
</select>
Entries Per Page
<input name="submit" type="submit" value="Go" />
</form>
<?php

while ($row = mysql_fetch_assoc($r)) {

echo 'Loop your results here';

}

$numofpages = $totalrows / $limit;

for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo "<b>".$i."</b> ";
}else{
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&lmt=".$limit."\"> $i </a> ";
}
}

if(($totalrows % $limit)!= 0){
if($i == $page){
echo "<b>".$i."</b> ";
}else{
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&lmt=".$limit."\"> $i </a> ";
}
}

} else {

echo 'No Results';

}

?>

[Dragosh]

justgowithit, your script is great! howether i see it doesnt do what i want. imagine i have 1000rows in the database and the maximum results per page are 5,there should be 200 links under these results : 1 2 3 4 5 6 7 8 9 10 11 12 13... that would occupy a lot of space + it's unprofessional. all i want is to have a pagination system as google or any other forum. it display prev 1 2 3 ... 6 7 ...10 11 12 next. thats what i want.

[d40sithui]

here is what i use. Basically you have a main query. you run it, get all the rows and id for each row.
assign it to a double array ($pages in this case). the row would be the page number and the col would represent the id value from the query. so for example
$pages[1][0] = first page, id at $pages[1][0]
$pages[1][1] first page, id at $pages[1][1]
$pages[3][7] third page, id at $pages[3][7]

the script would reprocess the query and double array every time you goto each page(refresh), but you wil only see the data for the current page you're on, which is assigned to $rec[];

//getting the page number
$page_num = clean_var(mysql_safe($_REQUEST['page_num'])); //current page number
if (empty($page_num) ¦¦!is_numeric($page_num)){
$page_num = 1;
}

//title
echo "<span class=\"title\">VPN Users</span><br>\n";

$limit = 10; //the number of records to display per page

/***** Main query *****/
$query = "select * from vpn_user order by last_name asc";
$result = mysql_query($query);

$number_of_pages = ceil(mysql_num_rows($result) / $limit); //number of pages duh
//echo "\$number_of_pages: $number_of_pages<br>";

//loops for # of pages
for($i = 1; $i <= $number_of_pages; $i++)
{
//loops for the number of records per page
for ($j = 1; $j <= $limit; $j++)
{
$record = mysql_fetch_assoc($result); //assigning records to array
$pages[$i][$j] = $record['id'];

//if page does not reach $limit records
if (empty($pages[$i][$j])) { $j = $limit + 1;}
}
}

//current data for page
for ($i = 1; $i <= $limit; $i++)
{
$rec[$i] = $pages[$page_num][$i];
}

/*****
Back Button
*****/
if ($page_num > 1)
{
$temp_page = $page_num - 1;
if (!empty($pages[$temp_page]))
{
//echo "<div style=\"position:absolute; top:80; left:50; width:80; height:2;\"> \n";
echo " <a class=\"a3\" href = \"main.php?view=all_users&page_num=$temp_page\"> Back </a>\n";
//echo "</div>\n";
}
}//ends back button

/*****
page numbers
*****/
$num_links = 5; //number of links to provide (backwards and forward)

/*****
backward links
*****/
if ($page_num > 1)
{

//link to page 1
if ($page_num > $num_links)
{
echo "<a class=\"a3\" href = \"main.php?view=all_users&page_num=1\"> 1 </a><font color=336699 size=2> ...</font>\n";
}
//the else algorithm does not work with numbers smaller than $num_links
if ($page_num < $num_links )
{

for ($i = 1; $i < $page_num; $i++)
{
if(!empty($pages[$i]))
{
echo "<a class=\"a3\" href = \"main.php?view=all_users&page_num=$i\"> $i </a>\n";
}
}
}

else
{
//loops for current page
for ($i = abs($num_links-$page_num) ; $i < $page_num; $i++)
{
if (!empty($pages[$i]))
{
echo "<a class=\"a3\" href = \"main.php?view=all_users&page_num=$i\"> $i </a>\n";
}
}
}

}//ends backwards links

/*****
current page
*****/
echo "<font color=dc104e > $page_num </font> \n";

/*****
forward links
*****/
if ($page_num < sizeof($pages))
{
for ($i = $page_num+1; $i < $page_num+$num_links; $i++)
{
if (!empty($pages[$i]))
{
echo "<a class = \"a3\" href = \"main.php?view=all_users&page_num=$i\"> $i </a>\n";

}//ends if
}//ends for

}//ends forward links

/*****
link to last page
*****/
if ($page_num <= ($total_pages - $num_links)){
echo "<font color=336699 size=2> ...</font> <a class = \"a3\" href = \"main.php?view=all_users&page_num=$total_pages\"> $total_pages </a>\t\n";
}

/*****
forward button
*****/
if ($page_num < sizeof ($pages))
{
$temp_page2 = $page_num+1;
if (!empty($pages[$temp_page2]))
{
//echo "<div style=\"position:absolute; top:80; left:500; width:80; height:2;\"> \n";
echo "<a class=\"a3\" href = \"main.php?view=all_users&page_num=$temp_page2\"> Next </a>\n";
//echo "</div>\n";
}

}//ends forward button

/*****
Display data here
*****/

[justgowithit]

Cool d40sithui - but where's the second select statement with LIMIT clause?

I can see you're looping results here:

$record = mysql_fetch_assoc($result); //assigning records to array

but that's from the original db query:

$query = "select * from vpn_user order by last_name asc";

.... the result will always be from the same total set, right?

[d40sithui]

$pages[$i][$j] = $record['id']; //each id is stored in one big double array
i never figured to use the LIMIT as part of the query. you will not need it in this algorithm. pretty much the results will always be the same total set, its just that the variable $page_num will determine which part of the $pages[][] array to display. of course you can always edit the query to your preference.

lets say you have 55 records, each with unique ids of course.lets say ids are 1,2,3,4...55
1) the query will select all 55 records.
2) assigns each id to $pages[][], so youll have a 6x10 double array
3) the dimension of the $pages is determined by $limit. so if $limit is 10, its gong to be 6x10(55/10 rounded up...you cant have 5.5x10). if $limit is 5, its going to be 11x5 (55/5)--thats 11 pages in total! if $limit is 1, its goin to be 55x1(55 pages)
4) Now, lets say your $limit is 10, youll have 6 pages.
5 pages with 10 records on each and the last one with 5 records. your $pages would be a 6x10 double array(6 rows 10 columns).
5) Lets say you're on page 3, for the page to only display records for this page, it needs read the third row which is $pages[3][1], $pages[3][2], $pages[3][3]... to get the ids of which record to query. the row value can be derived from the $page_num variable, whchi can be passed and incremented or decremented in your URL., this is done through the links that is provided.
6) if you have more than $num_links page, there will be links to link the page to the last page or the first page depending on where you are in the navigation.

lol i hope this makes sense. you should try this out and let me know how it works(or not). post here if you run into problems

[Dragosh]

Thanks a lot guys, i'll check it!

[Dragosh]

It doesnt look as i wanted. well it seems that i'll have to code it for myself :) though i have less knowledge of php and mysql. if i'll make it,i'll show it here. God help me! ufff...

[d40sithui]

ha if anything you'll learn alot by doin things yourself. good luck

[Dragosh]

here is all the code (pagination+ smf function constructindexpage). now everything works ok, i mean there is no error showing,howether function constructindexpage does not show the pages. how can i call this function in my case?

<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("facts") or die(mysql_error());

//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * FROM content") or die(mysql_error());
$rows = mysql_num_rows($data);

//This is the number of results displayed per page
$page_rows = 4;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
//This is your query again, the same one... the only difference is we add $max into it
$result = mysql_query("SELECT * FROM content $max") or die(mysql_error());

//This is where you display your query results
while($row = mysql_fetch_array( $result ))
{
Print $row['facts'];
echo "<br>";
}
echo "<p>";

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";

// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}

//just a spacer
echo " ---- ";

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}

// Constructs a page list.
// $pageindex = constructPageIndex($scripturl . '?board=' . $board, $_REQUEST['start'], $num_messages, $maxindex, true);
function constructPageIndex($base_url, &$pagenum, $last, $page_rows)
{

// Save whether $pagenum was less than 0 or not.
$pagenum_invalid = $pagenum < 0;

// Make sure $pagenum is a proper variable - not less than 0.
if ($pagenum_invalid)
$pagenum = 0;
// Not greater than the upper bound.
elseif ($pagenum >= $last)
$pagenum = max(0, (int) $last - (((int) $last % (int) $page_rows) == 0? $page_rows : ((int) $last % (int) $page_rows)));
// And it has to be a multiple of $page_rows!
else
$pagenum = max(0, (int) $pagenum - ((int) $pagenum % (int) $page_rows));

$base_link = '<a class="navPages" href="' . $_SERVER['PHP_SELF'] . 'pagenum=%d' . '">%s</a> ';

// Compact pages is off or on?

// If they didn't enter an odd value, pretend they did.
$PageContiguous = 3;

// Show the first page. (>1< ... 6 7 [8] 9 10 ... 15)
if ($pagenum > $page_rows * $PageContiguous)
$pageindex = sprintf($base_link, 0, '1');
else
$pageindex = '';

// Show the ... after the first page. (1 >...< 6 7 [8] 9 10 ... 15)
if ($pagenum > $page_rows * ($PageContiguous + 1))
$pageindex .= '<b> ... </b>';

// Show the pages before the current one. (1 ... >6 7< [8] 9 10 ... 15)
for ($nCont = $PageContiguous; $nCont >= 1; $nCont--)
if ($pagenum >= $page_rows * $nCont)
{
$tmpStart = $pagenum - $page_rows * $nCont;
$pageindex.= sprintf($base_link, $tmpStart, $tmpStart / $page_rows + 1);
}

// Show the current page. (1 ... 6 7 >[8]< 9 10 ... 15)
if (!$pagenum_invalid)
$pageindex .= '[<b>' . ($pagenum / $page_rows + 1) . '</b>] ';
else
$pageindex .= sprintf($base_link, $pagenum, $pagenum / $page_rows + 1);

// Show the pages after the current one... (1 ... 6 7 [8] >9 10< ... 15)
$tmpMaxPages = (int) (($last - 1) / $page_rows) * $page_rows;
for ($nCont = 1; $nCont <= $PageContiguous; $nCont++)
if ($pagenum + $page_rows * $nCont <= $tmpMaxPages)
{
$tmpStart = $pagenum + $page_rows * $nCont;
$pageindex .= sprintf($base_link, $tmpStart, $tmpStart / $page_rows + 1);
}

// Show the '...' part near the end. (1 ... 6 7 [8] 9 10 >...< 15)
if ($pagenum + $page_rows * ($PageContiguous + 1) < $tmpMaxPages)
$pageindex .= '<b> ... </b>';

// Show the last number in the list. (1 ... 6 7 [8] 9 10 ... >15<)
if ($pagenum + $page_rows * $PageContiguous < $tmpMaxPages)
$pageindex .= sprintf($base_link, $tmpMaxPages, $tmpMaxPages / $page_rows + 1);

return $pageindex;
}

?>

[edited by: Dragosh at 2:36 pm (utc) on Aug. 10, 2007]

[justgowithit]

Post the function constructPageIndex, please.

[Dragosh]

i've already posted it. look at the code i posted earlier.

[Dragosh]

<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("facts") or die(mysql_error());

//This checks to see if there is a page number. If not, it will set it to page 1
if (!(isset($pagenum)))
{
$pagenum = 1;
}

//Here we count the number of results
//Edit $data to be your query
$data = mysql_query("SELECT * FROM content") or die(mysql_error());
$rows = mysql_num_rows($data);

//This is the number of results displayed per page
$page_rows = 4;

//This tells us the page number of our last page
$last = ceil($rows/$page_rows);

//this makes sure the page number isn't below one, or more than our maximum pages
if ($pagenum < 1)
{
$pagenum = 1;
}
elseif ($pagenum > $last)
{
$pagenum = $last;
}

//This sets the range to display in our query
$max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows;
//This is your query again, the same one... the only difference is we add $max into it
$result = mysql_query("SELECT * FROM content $max") or die(mysql_error());

//This is where you display your query results
while($row = mysql_fetch_array( $result ))
{
Print "* ". $row['facts'];
echo "<br>";
}
echo "<p>";

// This shows the user what page they are on, and the total number of pages
echo " --Page $pagenum of $last-- <p>";
/*
// First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page.
if ($pagenum == 1)
{ echo "<<-First <-Previous";
}
else
{
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=1'> <<-First</a> ";
echo " ";
$previous = $pagenum-1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$previous'> <-Previous</a> ";
}

//just a spacer
echo " ---- ";

//This does the same as above, only checking if we are on the last page, and then generating the Next and Last links
if ($pagenum == $last)
{ echo "Next -> Last ->>";
}
else {
$next = $pagenum+1;
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$next'>Next -></a> ";
echo " ";
echo " <a href='{$_SERVER['PHP_SELF']}?pagenum=$last'>Last ->></a> ";
}

*/
function constructPageIndex($start, $page_rows, $rows) { $pageindex='';
while($rows/$page_rows!= ceil($rows/$page_rows))
$rows ++;

$base_link = '<a class="navPages" href="' . $_SERVER['PHP_SELF'] . '?pagenum=%d' . '">%d</a> ';
$base_current = '<strong>[%d]</strong> ';

if( $start < 0 or $start > $rows/$page_rows)
$start = 1;

//Previous pages
if($start-1 < 4)
for($i = 1; $i < $start; $i ++)
$pageindex .= sprintf($base_link, $i, $i);
else {
$pageindex .= sprintf($base_link, 1, 1);
$pageindex .= '...';
for($i = $start-2; $i < $start; $i ++)
$pageindex .= sprintf($base_link, $i, $i);
}

// Current page
$pageindex .= sprintf($base_current, $start);

//Next pages
if($rows/$page_rows - $start < 4)
for($i = $start + 1; $i <= $rows/$page_rows; $i ++)
$pageindex .= sprintf($base_link, $i, $i);
else {
for($i = $start+1; $i < $start+3; $i ++)
$pageindex .= sprintf($base_link, $i, $i);
$pageindex .= '...';
$pageindex .= sprintf($base_link, $rows/$page_rows,$rows/$page_rows);
}
return $pageindex;
}
echo constructPageIndex($pagenum, $page_rows, $rows);
echo "<br />";

echo " <input Id=\"pagenumberinput\" size=\"1\" maxlength=\"5\" type=\"text\" /><input type=\"button\" value=\"Go\" onClick=\"javascript:window.location='". $_SERVER['PHP_SELF'] . "?pagenum='+document.getElementById('pagenumberinput').value;\" />";

?>

Nice,isn't it?

[Habtom]

Nice,isn't it?

Well yea, ;)

Any problems with that? :)

[justgowithit]

I've taken some time to write a pagination script that functions exactly like Google's pagination. The script will show 20 pages (assuming there are enough results) with a maximum of 10 pages behind the current page and 9 pages ahead of the current page.

I wrote it using a single $_GET for the page value that defaults to 1 if the script doesn't get what it wants. This should be more secure and probably faster than using multi-dimensional arrays for start/stop values for the query.

<?php

$limit = 10;
$start = 1;
$slice = 9;

$q = "SELECT * FROM table";
$r = mysql_query($q, conn());
$totalrows = mysql_num_rows($r);

if(!isset($_GET['page']) ¦¦!is_numeric($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

$numofpages = ceil($totalrows / $limit);
$limitvalue = $page * $limit - ($limit);

$q = "SELECT * FROM table LIMIT $limitvalue, $limit";
if ($r = mysql_query($q, conn())) {

//loop results here
while ($row = mysql_fetch_assoc($r)) {
echo $row['column'].'<br />';
}

if($page!= 1){
$pageprev = $page - 1;
echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$pageprev.'">PREV</a> - ';
}else{
echo "PREV - ";
}

if (($page + $slice) < $numofpages) {
$this_far = $page + $slice;
} else {
$this_far = $numofpages;
}

if (($start + $page) >= 10 && ($page - 10) > 0) {
$start = $page - 10;
}

for ($i = $start; $i <= $this_far; $i++){
if($i == $page){
echo "<u><b>".$i."</b></u> ";
}else{
echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$i.'">'.$i.'</a> ';
}
}

if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page + 1;
echo ' - <a href="'.$_SERVER['PHP_SELF'].'?page='.$pagenext.'">NEXT</a>';
}else{
echo " - NEXT";
}

}

?>