Re: previous & next buttons - Mailing list pgsql-general

I was just helped with this last week (see threat "Interfaces that support
cursors" that started on 10/10 if its in the archives".  The two ways to do it-
either issuing a selects with limit/offset modifiers or with a cursor.  On the
above thread today, it was posted that large datasets are going to significantly
slow the limit/offset style down but since it sounds like you are doing a web
app, you are faced with implementing a persistent connection to the database on
the backend (which was also my issue)

Over the weekend, I did the limit/offset method since I do have a large set of
data.  Basically I have an image browser that display Z images per page where Z
is caculated from X columns by Y rows (these can be changed in the script in
case my client ever want to change the format).  For example, each "page
display" there is represented by this query:

select id,name from files order by id limit $pagec offset $offset;

$offset=($page * $pagec) - $pagec;
$pagec = $cols * $rows;  # the number items per page- in you case 10

I only use prev and next buttom but you could use that logic to calculate the
full range of pages.  To find the last page, you could use this:

select ceil(count(*)::float/$pagec) from files;


Don't forget to check for use sillyness like pages less that 1 or pages greater
than your last page.

BTW, to do this with cursors a starting point is to look at PersistentPerl or
mod_perl and their respective documentation on how to do persistent database
connections.  The short answer is that you have to make database connection
global so that it can be checked in subsequent runs of your script.  The long
answer is to dig through the documentation.

Hope that help you!


Thanks to Doug McNaught and Jonathan Bartlett for helping me last week!!  You're
comments made for an enjoyable programming weekend  :)


Quoting Psybar Phreak <psybar_phreak@yahoo.com>:

> hi all,
>
> im developing a site in perl with mason on a postgreSQL backend.
>
> i currently have a page that lists all items in a table, but would like to
> do the
> " << PREVIOUS  1  2  3  4   NEXT>> "
>
> sort of thing - say LIMIT of 10 records to a page.
>
> can someone help me out.
>
> thanks
>
> PP
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>


--
Keith C. Perry
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com