Thread: Paging results

Paging results

From
Lynna Landstreet
Date:
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


Re: Paging results

From
"David Busby"
Date:
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


Re: Paging results

From
Ângelo Marcos Rigo
Date:
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

Re: Paging results

From
Ângelo Marcos Rigo
Date:
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

Re: Paging results

From
Ângelo Marcos Rigo
Date:
Do you have PEAR?

There is in
http://pear.php.net/package-info.php?package=DB_Pager,
the dbpager package, wich works very well

--- Lynna Landstreet <lynna@gallery44.org> escreveu: >
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

=====
Â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

Re: Paging results

From
Gerd Terlutter
Date:
Ângelo Marcos Rigo wrote:
> Do you have PEAR?
>
getting started with pear:
root@home#lynx -source http://go-pear.org | php
  you can run for test as normal user, than you have to configure the
variables bin_dir, doc_dir, data_dir and php_dir. Set this to your home,
you'll get a running pear-system.
greetings, Gerd

--
--------------------------------------------------------
# Gerd Terlutter        | Mueller+Blanck Software GmbH #
# gerd@MplusB.de        | Gutenbergring 38             #
# gerd.terlutter@web.de | D-22848 Noderstedt           #
# tel:0171/6992579      | tel:+49 40 500 171-1         #
# Buero:040/500171-17   | fax:+49 40 500 171-71        #
--------------------------------------------------------



Re: Paging results

From
Gerd Terlutter
Date:
Ângelo Marcos Rigo wrote:
> Do you have PEAR?
>
> There is in
> http://pear.php.net/package-info.php?package=DB_Pager,
> the dbpager package, wich works very well
>>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
Hi all,
PEAR is general a good idea. I use PEAR::DB as wrapper for access do
different RDBMS, in my case PG and MySQL. nxet week i'll start with
transaction on MySQL3.23.51, but don't know what happends. Other
functionality is given during use PEAR::DB. This package supports 10 or
12 RDBMS. To convert MySQL to PG try this:
http://ns2.ziet.zhitomir.ua/~fonin/projects/my2pg/my2pg_man.html
Another good tipp is this:
http://www.rot13.org/~dpavlin/sql.html
If you have a good config-file for your source, you can switch by
changing only the param 'dbtype' e.g. psql or mysql.
excuse my bad english,
Gerd

--
--------------------------------------------------------
# Gerd Terlutter        | Mueller+Blanck Software GmbH #
# gerd@MplusB.de        | Gutenbergring 38             #
# gerd.terlutter@web.de | D-22848 Noderstedt           #
# tel:0171/6992579      | tel:+49 40 500 171-1         #
# Buero:040/500171-17   | fax:+49 40 500 171-71        #
--------------------------------------------------------



Re: Paging results

From
Lynna Landstreet
Date:
on 8/11/03 2:14 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote:

> Do you have PEAR?
>
> There is in
> http://pear.php.net/package-info.php?package=DB_Pager,
> the dbpager package, wich works very well

I'd never heard of it before - I'm fairly new to PHP. I just looked through
the documentation a bit, but I'm a bit confused - is this something I can
actually install on my own, in the gallery's home directory, or is it
something that our web host would have to install? We don't have our own
server; we're just a on a shared hosting plan.


Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org


Re: Paging results

From
Lynna Landstreet
Date:
on 8/11/03 1:31 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote:

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

Thank you very much - I've implemented this on one of my results pages as a
test, and it seems to be partially working - the results are limited
properly, and there are no errors, but the next and previous links aren't
quite working right.

On the first page, the prev link points to offset 0 and page 1, which I
guess is correct, but the next link leaves offset blank, and has page 0,
instead of having offset 50 (that's what I set $limit to) and page 2 as I
would think it should.

If I enter those values manually in the URL -
http://www.gallery44.org/db/artists_browse_paged.php?offset=50&pgnum=2 - the
second page displays correctly, but again the prev and next links don't work
right. The prev page here shows pgnum=1, which is correct, but it shows
offset as 30 when it should be 50. And the next link shows exactly what it
did on page 1 - offset=&pgnum=0. I've looked at the code, but I don't think
I'm following it well enough to be sure what's going wrong. I've attached a
copy of the file

I kept the code pretty much as it was except for the part where the results
are displayed, and a couple of very minor tweaks (changed the pg_query and
pg_num_rows to pg_exec and pg_numrows because I'm using PHP 4.1, changed the
part where it selects the whole table and uses pg_numrows to count it to a
select count because it seemed more efficient, and changed $conn to the
database connection I'd already opened, $db).

If you or anyone else could suggest what might be going wrong, I'd very much
appreciate it. Here's what I've got:

First part (before displaying query results):

        // Paging script - 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
        if (!$db) {
        echo "An error occured - no database connection exists.\n";
        exit;
        }

        // Initialize variables.
        $limit=50; // rows to return
        $numresults=pg_exec("SELECT COUNT(*) FROM artists"); //PostgreSQL
        // $numrows=pg_numrows($numresults);

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

Then comes the display of the results, which is working fine.

Second part:

        // 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 "\n<p class=\"small\">";
        // 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";

            }
        }

Many thanks,

Lynna
--
Resource Centre Database Coordinator
Gallery 44
www.gallery44.org


Re: Paging results

From
"scott.marlowe"
Date:
On Wed, 13 Aug 2003, Lynna Landstreet wrote:

> on 8/11/03 2:14 PM, Ângelo Marcos Rigo at angelo_rigo@yahoo.com.br wrote:
>
> > Do you have PEAR?
> >
> > There is in
> > http://pear.php.net/package-info.php?package=DB_Pager,
> > the dbpager package, wich works very well
>
> I'd never heard of it before - I'm fairly new to PHP. I just looked through
> the documentation a bit, but I'm a bit confused - is this something I can
> actually install on my own, in the gallery's home directory, or is it
> something that our web host would have to install? We don't have our own
> server; we're just a on a shared hosting plan.

PEAR, and about 12 or so other packages, are usually distributed with PHP
and found in the /usr/local/lib/php directory on most Unix boxes.