Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 59F389FB893 for ; Wed, 28 Mar 2007 18:21:49 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 65952-06 for ; Wed, 28 Mar 2007 18:21:42 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.potentialtech.com (internet.potentialtech.com [66.167.251.6]) by postgresql.org (Postfix) with ESMTP id 9A75D9FBA76 for ; Wed, 28 Mar 2007 18:21:45 -0300 (ADT) Received: from vanquish.pgh.priv.collaborativefusion.com (pr40.pitbpa0.pub.collaborativefusion.com [206.210.89.202]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.potentialtech.com (Postfix) with ESMTP id 30356EBC76; Wed, 28 Mar 2007 17:21:44 -0400 (EDT) Date: Wed, 28 Mar 2007 17:21:43 -0400 From: Bill Moran To: Lynna Landstreet Cc: Subject: Re: Retrieving result of COUNT(*) with PHP Message-Id: <20070328172143.81cac667.wmoran@potentialtech.com> In-Reply-To: References: X-Mailer: Sylpheed 2.3.1 (GTK+ 2.10.11; i386-portbld-freebsd6.1) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.133 tagged_above=0 required=5 tests=AWL=0.133, BAYES_50=0.001, SPF_PASS=-0.001 X-Spam-Level: X-Archive-Number: 200703/57 X-Sequence-Number: 3769 The upshot of what a lot of other people are saying is, "what you are trying to do should work." Your results are atypical. Despite the number of answers that suggest you try a slightly different approach, the way you're doing it should work, I've used it without problem. I would recommend you provide an actual code sample, to ensure you're not making some minor mistake. If it turns out that this doesn't work on your setup, I would suggest that it's probably a PHP problem and not a Postgres one. More specifically, it's probably a problem with the specific version/ packaging/installation/OS of your PHP, as I've never seen the type of behaviour you're describing. In response to Lynna Landstreet : > > I'm trying to use a SELECT COUNT(*) to count how many results would be > retrieved from a particular query (as part of the process of paginating > search results). > > But I'm having trouble figuring out how to retrieve the result of the count > in PHP. The result on its own is a resource rather than a specific value, > but when I try to retrieve the result via pg_fetch_result, some kind of > weird math error happens and I get a huge number that bears no resemblance > to the number of results the query actually gets when it runs (1,714,608 for > a query that in actuality produces three results). > > I tried using an AS clause (SELECT COUNT(*) AS result_count FROM etc. WHERE > etc.) to give the result a name, but that didn't help, and when I tried > using pg_fetch_all on the result to see exactly what it was retrieving, I > got this: > > Array > ( > [0] => Array > ( > [result_count] => 1714608 > ) > > ) > > Again with the weird number. And yet, if I run the exact same query in the > SQL window of phpPgAdmin, I get the proper result count (3 in this > instance). > > Does anyone know what's going on here? Can I just not use SELECT COUNT(*) > with PHP at all? > > I originally had the script running the actual query and then counting the > results, and then running it again with LIMIT and OFFSET to get one page's > worth of results, but it seemed wasteful to do it that way, so I was trying > to do it more efficiently... :-/ > > Thanks, > > Lynna > > -- > Spider Silk Design - http://www.spidersilk.net > 509 St Clair W Box 73576, Toronto ON Canada M6C 1C0 > Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289 > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bill Moran http://www.potentialtech.com