Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id DBF4D9FB7C9 for ; Wed, 28 Mar 2007 18:20:06 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 07086-02 for ; Wed, 28 Mar 2007 18:19:57 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from cooma-1.andyshellam.eu (cooma-1.andyshellam.eu [82.165.38.62]) by postgresql.org (Postfix) with ESMTP id C74049FB7CE for ; Wed, 28 Mar 2007 18:20:00 -0300 (ADT) Received: from 81-86-233-75.dsl.pipex.com ([81.86.233.75]:2822 helo=[10.100.9.2]) by cooma-1.andyshellam.eu with esmtpa (Exim 4.66) (envelope-from ) id 1HWfcy-0001y4-Dd for pgsql-php@postgresql.org; Wed, 28 Mar 2007 22:24:20 +0100 Message-ID: <460ADBDD.5020205@mailnetwork.co.uk> Date: Wed, 28 Mar 2007 22:19:25 +0100 From: Andy Shellam Reply-To: andy.shellam-lists@mailnetwork.co.uk Organization: www.AndyShellam.EU User-Agent: Thunderbird 1.5.0.10 (Windows/20070221) MIME-Version: 1.0 To: pgsql-php@postgresql.org Subject: Re: Retrieving result of COUNT(*) with PHP References: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> In-Reply-To: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50=0.001, SPF_PASS=-0.001 X-Spam-Level: X-Archive-Number: 200703/56 X-Sequence-Number: 3768 See note below... Mihail Mihailov wrote: > Hi, > > actually, I don't think you need pg_fetch_all. > I use pg_fetch_row for the purpose. What you get with the SELECT > clause is one row. > > E.g. like this: > $res = pg_fetch_row(pg_query("SELECT COUNT(*) AS result_count FROM > etc. WHERE etc.")); //Not a very good style :-), one should check if > the query runs > $count = $res[0]; > > Another way to calculate number of rows in the result is to use > pg_num_rows function. > $res = pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc."); > $count = pg_num_rows($res); ^^ Will this not just return 1? As there is only 1 row in the returned dataset. Can't say I've ever tried but that's what I'd think would happen. Personally I'd do "SELECT FROM etc" then call pg_num_rows. Although I've never had problems with COUNT(*) working. Perhaps try doing SELECT COUNT() as num_of_rows FROM etc? Andy. > > Enjoy! > > Mihail > > > > Quoting Lynna Landstreet : > >> Hi there, >> >> 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 >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > > !DSPAM:37,460ad90b103001377313056! > >