Re: Paging results - Mailing list 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@pnts.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@gallery44.org>
> To: <pgsql-php@postgresql.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@postgresql.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@yahoo.com.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