X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7E3D8DABE9 for ; Wed, 23 Nov 2005 12:14:21 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25705-06 for ; Wed, 23 Nov 2005 16:14:18 +0000 (GMT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from xproxy.gmail.com (xproxy.gmail.com [66.249.82.199]) by svr1.postgresql.org (Postfix) with ESMTP id 1F9DDD7AD7 for ; Wed, 23 Nov 2005 12:14:17 -0400 (AST) Received: by xproxy.gmail.com with SMTP id t5so1228246wxc for ; Wed, 23 Nov 2005 08:14:16 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition; b=RvJnE9OMXaUBhZuw4BxQNwHgnRDfIfkLBmj/3gTaQClenCnIOKvcxHd3cZ+PBlh6EFeEOCCYCr9VN4fkI1gI1rdwnhJCl/k555VNNt2pXoifFvXqP6XcQ/S+dkeus2TMhwaVcBdOCKWH6veaofzYMI3FJ5GGqrQ2CmX5XzNILJA= Received: by 10.65.137.14 with SMTP id p14mr6259222qbn; Wed, 23 Nov 2005 08:14:16 -0800 (PST) Received: by 10.65.224.16 with HTTP; Wed, 23 Nov 2005 08:14:16 -0800 (PST) Message-ID: Date: Wed, 23 Nov 2005 16:14:16 +0000 From: Neil Saunders To: pgsql-php@postgresql.org Subject: Pagination of search results MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/23 X-Sequence-Number: 3340 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 ) AS c WHERE 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 receive= d. Kind Regards, Neil Saunders.