From: | Ângelo Marcos Rigo <angelo_rigo(at)yahoo(dot)com(dot)br> |
---|---|
To: | David Busby <busby(at)pnts(dot)com>, Lynna Landstreet <lynna(at)gallery44(dot)org>, pgsql-php(at)postgresql(dot)org |
Subject: | Re: Paging results |
Date: | 2003-08-11 17:31:47 |
Message-ID: | 20030811173147.77918.qmail@web14808.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-php |
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] ";
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>
\n";
}
else
{
$prevoffset=$offset-20;
$cpgnum = intval($prevoffset/$limit)+1;
print "<a
href=\"$PHP_SELF?offset=$prevoffset&pgnum=$cpgnum\">PREV</a>
\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>
\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
From | Date | Subject | |
---|---|---|---|
Next Message | Ângelo Marcos Rigo | 2003-08-11 17:31:48 | Re: Paging results |
Previous Message | Brad Bulger | 2003-08-10 04:53:17 | Re: functions with plpgsql |