From: Damián Culotta <dculotta@fibertel.com.ar>
To: Cstdenis <cstdenis@voicio.com>
To: pgsql-php@postgresql.org
Subject: Re: Pagination of search results
Date: Sun, 27 Nov 2005 16:33:35 -0300
Message-ID: <003601c5f389$759a9020$6401a8c0@workstation> (raw)
References: <ddcd549e0511230814s1d5f235en17a25b7ca9edea36@mail.gmail.com>
<000a01c5f2dc$a3bfa560$6501a8c0@chris>
If you sotre the count into a session, maybe you show incorrect information.
It's possible that someone insert a new record when you are changing from
one page to another.
Sorry, my english it's very bad.
----- Original Message -----
From: "Cstdenis" <cstdenis@voicio.com>
To: "Neil Saunders" <n.j.saunders@gmail.com>; <pgsql-php@postgresql.org>
Sent: Saturday, November 26, 2005 5:59 AM
Subject: Re: [PHP] Pagination of search results
> Get the count once and store it in a session.
>
> ----- Original Message -----
> From: "Neil Saunders" <n.j.saunders@gmail.com>
> To: <pgsql-php@postgresql.org>
> Sent: Wednesday, November 23, 2005 8:14 AM
> Subject: [PHP] Pagination of search results
>
>
> Hi,
>
> I'm writing a search engine, the results of which will displayed in
> blocks of 25, paginated. I will also display the number of records
> found (1-25 of 345).
>
> I'm trying to determine the most efficient way to implement this.
> Since the session is dropped after the script is terminated, I'm
> ruling our cursors. Also, cursors would prevent me from retrieving the
> total number of records potentially returned from a search, and so I
> couldn't display the correct number of page links. Also, this would
> require persistent connections, which I'd like to avoid for a number
> of reasons.
>
> The best I've come up with is "SELECT b.*, c.cnt FROM table AS b,
> (SELECT COUNT(1) AS cnt FROM table WHERE <LOTS OF CLAUSES>) AS c WHERE
> <SAME LOT OF CLAUSES> LIMIT 25 OFFSET 0"
>
> However this means that the query is essentially getting executed
> twice for each page.
>
> Is there any better way of implementing this? All advice gratefully
> received.
>
> Kind Regards,
>
> Neil Saunders.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: dculotta@fibertel.com.ar, cstdenis@voicio.com
Subject: Re: Pagination of search results
In-Reply-To: <003601c5f389$759a9020$6401a8c0@workstation>
References: <ddcd549e0511230814s1d5f235en17a25b7ca9edea36@mail.gmail.com> <000a01c5f2dc$a3bfa560$6501a8c0@chris> <003601c5f389$759a9020$6401a8c0@workstation>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox