Re: Paging results

I have this script (please adapt the section where the
results are showed)

<?php
// Request for parameters.
$offset = $_REQUEST['offset'];
$pgnum = $_REQUEST['pgnum'];

// Make that they are integer - security.
settype($offset, 'integer');
settype($pgnum, 'integer');

// Open database, i.e PostgreSQL
$connuserpostgres");
if (!$conn) {
echo "An error occured.\n";
exit;
}

// Initialize variables.
$limit=20; // rows to return
$numresults=pg_query("select * from table"); //
PostgreSQL
$numrows=pg_num_rows($numresults);

// next determine if offset has been passed to
script, if not use 0
if (empty($offset)) {
$offset=0;
$pgnum=1;
}

// get results
$result=pg_query("select autor, titulo from table
limit $limit offset $offset"); // PostgreSQL

// now you can display the results returned
//echo "OffSet ".$offset." Page# ".$pgnum."<br><br>";

echo "<table><tr>";
echo "<td><b>Autor</b></td>";
echo "<td><b>Titulo</b></td>";
echo "</tr>";
while ($row = pg_fetch_array($result, $i)) {
echo "<tr><td align=\"left\">";
//$arr = pg_fetch_array($result,($i));
for ($j=0; $j < count($row); $j++) {
/*
echo "<tr><td align=\"left\" colspan=\"2\">";
echo "$row[$j]&nbsp;";
echo "</td></tr>";
*/

echo "$row[$j]";
//echo " $j['autor']";


}
echo "</td></tr>";
//echo "<BR>";
}
echo "</table>";
// calculate number of pages needing links
$pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless
there is a remainder from division
if ($numrows%$limit) {
// has remainder so add one page
$pages++;
}

echo "<font size=1>";
// next we need to do the links to other results
if (pages!=1)
{
if ($pgnum==1) {
print "<a
href=\"$PHP_SELF?offset=0&pgnum=1\">PREV</a> &nbsp;
\n";
}
else
{
$prevoffset=$offset-20;
$cpgnum = intval($prevoffset/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$prevoffset&pgnum=$cpgnum\">PREV</a>
&nbsp; \n";
}
}

for ($i=1;$i<=$pages;$i++) { // loop thru
$newoffset=$limit*($i-1);
$cpgnum = $i;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">$cpgnum</a>
&nbsp; \n";
}

// check to see if last page
if ($pages!=1)
{
if ($pgnum<$pages) {
$newoffset=$offset+$limit;
$cpgnum = intval(($offset+$limit)/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$cpgnum\">NEXT</a><p>\n";

}
else
{
print "<a
href=\"$PHP_SELF?offset=$newoffset&pgnum=$pages\">NEXT</a><p>\n";

}
}

// Close database.
pg_close($conn);
?>

--- David Busby <busby(at)pnts(dot)com> escreveu: > Paging
with PostgreSQL is super easy!
> select * from table where (x=y) offset 0 limit 30;
> Gives you the first 30 matches, then do
> select * from table where (x=y) offset 30 limit
> 30;
> This will give the next 30, super easy!
>
> Here's a sample of how I use it in a script
>
> // Collect offset
> $offset = isset($_GET['offset'])?$_GET['offset']:0;
> // Now the links for Prev/Next
> if ($offset >= 30) echo "|<a
>
href='/contents.php?".$qs."offset=".($offset-30)."'>Back
> 30</a>";
> echo "|<a
>
href='/contents.php?".$qs."offset=".($offset+30)."'>Next
> 30</a>";
> // Query
> $rs = pg_exec($db,"select id,name from stuff order
> by name offset $offset
> limit 30;");
>
> /B
>
>
> ----- Original Message -----
> From: "Lynna Landstreet" <lynna(at)gallery44(dot)org>
> To: <pgsql-php(at)postgresql(dot)org>
> Sent: Friday, August 08, 2003 13:30
> Subject: [PHP] Paging results
>
>
> > HI there,
> >
> > Thanks to everyone who helped with my keyword
> problem - I think I thanked
> > them all individually but I thought I should
> mention it here too.
> >
> > Now, a new question:
> >
> > Does anyone know if there's a PHP class anywhere
> out there for paging
> > results from a PostgreSQL query, similar to
> Paginator or ezResults which
> do
> > that for MySQL? Or do I have to do the code for
> that from scratch?
> >
> > Alternatively, would it be difficult to adapt one
> of those to working with
> > PostgreSQL instead of MySQL?
> >
> >
> > Lynna
> > --
> > Resource Centre Database Coordinator
> > Gallery 44
> > www.gallery44.org
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

=====
Ângelo Marcos Rigo
AMR Informática
(51) 3348 0870
Rua Pe. Alois Kades 400/210
Porto Alegre /RS/Brasil
http://amr.freezope.org
angelo_rigo(at)yahoo(dot)com(dot)br

_______________________________________________________________________
Conheça o novo Cadê? - Mais rápido, mais fácil e mais preciso.
Toda a web, 42 milhões de páginas brasileiras e nova busca por imagens!
http://www.cade.com.br