Page 1 of 1
Pagniation Advice
Posted: Sat Oct 21, 2006 8:19 am
by facets
Hi All,
We're using this code snippet to display retrieved pages from our db.
It's creating too many numbers. How could I paginate this?
I've tried various things like using '/10' on calculations but haven't found an acceptable result.
tia, Will/
Code: Select all
if ( $startrow - $this->pagesize > 0 ) { $prev = $startrow - $this->pagesize; } else { $prev = 0; }
$content .= " <a href = ".$script_name."?".$this->passargs."&fname=".$fname."&searchtext=".$searchtext."&startrow=".$prev.">Prev</a>\n";
for ( $i = 0; $i <= $this->numrows / $this->pagesize; $i++)
{
$startmp = $this->pagesize * $i;
if ( $i != 0 ) { $content .= "<b>,</b> "; }
if ( $startmp == $startrow )
{
$fontclass = "medium";
$maxpages = number_format($this->numrows / $this->pagesize, "0");
if ( $i < $maxpages ) { $next = $this->pagesize * ($i+1); } else { $next = $this->pagesize * $i; }
} else { $fontclass=""; }
$content .= "<a href = ".$script_name."?".$this->passargs."&fname=".$fname."&searchtext=".$searchtext."&startrow=" . $startmp." ><font class=".$fontclass.">".$i . "</font></a>";
}
$content .= " <a href = ".$script_name."?".$this->passargs."&fname=".$fname."&searchtext=".$searchtext."&startrow=".$next.">Next</a>\n";
Posted: Sat Oct 21, 2006 10:42 am
by AKA Panama Jack
Well, here is basically a generic one I am using in our game. It also uses the Template Lite templating engine for the presentation code and ADOdb Lite for accessing the database. Even with using those the code can easily be modified to work without using those PHP classes.
Code: Select all
// Make sure the page is set to 1 if the page variable isn't set.
if(empty($page))
{
$page = 1;
}
// How many items you want shown per page.
$entries_per_page = 25;
// Get the maximum number of entries availible
$query = "SELECT count(planet_id) as total FROM {$db_prefix}planets WHERE team=$playerinfo[team]";
$res = $db->SelectLimit($query, 1);
$totalentries = $res->fields['total'];
$res->close();
// Get the first element number to be pulled from the database using the Select Limit query.
$start = ($page - 1) * $entries_per_page;
// Calculate the total number of pages
$totalpages = ceil($totalentries / $entries_per_page);
// Send the current page number to the template
$template_object->assign("currentpage", $page);
// Calculate the next page number. If there isn't a next page number keep the next page number the same as the current page number
if($page < $totalpages)
{
$next = $page + 1;
}
else
{
$next = $page;
}
$template_object->assign("nextpage", $next);
// Calculate the previous page number. If the previous page number is 1 keep the prefvious page number the same as the current page number
if($page > 1)
{
$prev = $page - 1;
}
else
{
$prev = 1;
}
$template_object->assign("previouspage", $prev);
$template_object->assign("totalpages", $totalpages);
// Send the total pages plus 1 to the template. This is for use with the {FOR} loop tag when creating the drop down menu selection.
$template_object->assign("for_totalpages", ($totalpages + 1));
// Building the query to pull out the selected page of data.
$query = "SELECT {$db_prefix}universe.sector_name, {$db_prefix}planets.* FROM {$db_prefix}planets, {$db_prefix}universe WHERE {$db_prefix}planets.team=$playerinfo[team] and {$db_prefix}universe.sector_id ={$db_prefix}planets.sector_id";
if(!empty($sort))
{
$query .= " ORDER BY";
if($sort == "name")
{
$query .= " $sort ASC";
}
elseif($sort == "fighter" || $sort == "sensors" || $sort == "beams" || $sort == "torp_launchers" ||
$sort == "shields" || $sort == "cloak" || $sort == "owner" || $sort == "base" || $sort == "jammer")
{
$query .= " $sort DESC, sector_id ASC";
}
else
{
$query .= " sector_id ASC";
}
}
else
{
$query .= " ORDER BY sector_id ASC";
}
// Use the SelectLimit function of ADOdb Lite to set the starting position for retrieving the data for your page and the total number of elements to retrieve
$res = $db->SelectLimit($query, $entries_per_page, $start);
This is the presentation logic using Template Lite (Smarty compatible)
Code: Select all
{* Do not show page bar if there is only one page *}
{if $totalpages > 1}
<TABLE border=0 cellpadding=2 cellspacing=1 width="100%">
<form action="team_defenses.php" method="post">
<TR>
<td align="left" width="33%">
{* only show the previous link if the current page isn't the first page *}
{if $currentpage != 1}
<a href="team_defenses.php?page={$previouspage}&sort={$sort}">Previous Page</a>
{else}
{/if}
</td>
<TD align='center' width="33%">
Select Page: <select name="page">
{* Build the drop down menu and select the current page being viewed *}
{ for start=1 stop=$for_totalpages step=1 value=i }
<option value="{$i}"
{if $currentpage == $i}
selected
{/if}
> Page: {$i} </option>
{/for}
</select>
<input type="submit" value="Submit">
</TD>
<td align="right" width="33%">
{* only show the next link if the current page isn't the last page *}
{if $currentpage != $totalpages}
<a href="team_defenses.php?page={$nextpage}&sort={$sort}">Next Page</a>
{else}
{/if}
</td>
</tr>
<input type="hidden" name="sort" value="{$sort}">
</form>
</table>
{/if}
The above may look overly complicated but it is very simple and easy to modify. Maybe someone will find it of use.
Here is an example of it in use...

Posted: Sat Oct 21, 2006 10:55 am
by bokehman
Code: Select all
<?php
$content = null;
$page = isset($_GET['page']) ? $_GET['page'] : null ;
$results_per_page = 10;
$tablename = 'tablename';
$where = "WHERE `col_name` = 'something'";
$clauses = 'ORDER BY `col_name` DESC ';
db_connect(); // DB connect stuff
$query = "SELECT COUNT(*) as `count` FROM `$tablename` $where";
$result = mysql_query($query);
$row = mysql_fetch_assoc($result);
$num_rows = $row['count'];
// make sure page is within range
$total_pages = $num_rows ? ceil($num_rows / $results_per_page) : 1 ;
$page = ((is_numeric($page)) and ($page >= 1) and ($page <= $total_pages)) ? (int)$page : 1 ;
$query = "SELECT * FROM `$tablename` $where $clauses LIMIT $results_per_page OFFSET ".(($results_per_page * $page) - $results_per_page);
$result = mysql_query($query);
if(mysql_num_rows($result))
{
$content .= '<div>'."\n\n";
while($row = mysql_fetch_assoc($result))
{
$content .= '<p>'.$row['col_name'].'</p>';
}
$content .= '</div>';
$content .= pagination_links($page, $num_rows, $results_per_page);
}
echo $content;
function pagination_links($page, $num_rows, $results_per_page, $each_direction = 5)
{
$word_for_previous = 'anterior';
$word_for_next = 'siguiente';
$total_pages = $num_rows ? ceil($num_rows / $results_per_page) : 1 ;
$page = ((is_numeric($page)) and ($page >= 1) and ($page <= $total_pages)) ? (int)$page : 1 ;
$output = null;
if($page > 1)
{
$output .= '<a href="'.htmlentities($_SERVER['PHP_SELF']).'?page='.($page - 1).'">'.$word_for_previous.'</a> | '."\n";
}
for($i = $page - $each_direction; $i <= $page + $each_direction; $i++)
{
if(($i > 0) and ($i <= $total_pages))
{
$output .= isset($spacer) ? $spacer : null ;
$spacer = ' | '."\n";
if($page != $i)
{
$output .= '<a href="'.htmlentities($_SERVER['PHP_SELF']).'?page='.$i.'">'.$i.'</a>'."\n";
}
else
{
$output .= $i."\n";
}
}
}
if($page < $total_pages)
{
$output .= ' | <a href="'.htmlentities($_SERVER['PHP_SELF']).'?page='.($page + 1).'">'.$word_for_next.'</a>'."\n";
}
return '<p class="pagination-links">'."\n".$output."\n".'</p>';
}
?>