Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4D3E19FBB45 for ; Wed, 28 Mar 2007 18:05:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 06254-06 for ; Wed, 28 Mar 2007 18:05:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from alsikeapila.uta.fi (alsikeapila.uta.fi [153.1.1.44]) by postgresql.org (Postfix) with ESMTP id 4940D9FB967 for ; Wed, 28 Mar 2007 18:05:16 -0300 (ADT) Received: from valkoapila.uta.fi (valkoapila.uta.fi [153.1.1.42]) by alsikeapila.uta.fi (8.13.8/8.13.8) with ESMTP id l2SL5E29018536 for ; Thu, 29 Mar 2007 00:05:14 +0300 (EEST) Received: from apila.uta.fi (localhost.localdomain [127.0.0.1]) by valkoapila.uta.fi (8.13.8/8.13.8) with ESMTP id l2SL5BEL021969 for ; Thu, 29 Mar 2007 00:05:11 +0300 Received: from pippuri.uta.fi (pippuri.uta.fi [153.1.6.42]) by apila.uta.fi (8.13.8/8.13.8) with ESMTP id l2SL5B4I012294 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Thu, 29 Mar 2007 00:05:11 +0300 (EEST) Received: from pippuri.uta.fi (localhost.localdomain [127.0.0.1]) by pippuri.uta.fi (8.13.4/8.13.4/Debian-3sarge3) with ESMTP id l2SL5BjR020833 for ; Thu, 29 Mar 2007 00:05:11 +0300 Received: (from wwwroot@localhost) by pippuri.uta.fi (8.13.4/8.13.4/Submit) id l2SL5Ba2020832 for pgsql-php@postgresql.org; Thu, 29 Mar 2007 00:05:11 +0300 Received: from a81-197-44-234.elisa-laajakaista.fi (a81-197-44-234.elisa-laajakaista.fi [81.197.44.234]) by imp3.uta.fi (Horde MIME library) with HTTP; Thu, 29 Mar 2007 00:05:11 +0300 Message-ID: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> Date: Thu, 29 Mar 2007 00:05:11 +0300 From: Mihail Mihailov To: pgsql-php@postgresql.org Subject: Re: Retrieving result of COUNT(*) with PHP References: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8; DelSp="Yes"; format="flowed" Content-Disposition: inline Content-Transfer-Encoding: quoted-printable User-Agent: Internet Messaging Program (IMP) H3 (4.1.5-cvs) X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-3.0 (apila.uta.fi [153.1.1.41]); Thu, 29 Mar 2007 00:05:11 +0300 (EEST) X-Scanned-By: MIMEDefang 2.61 on 153.1.1.42 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/53 X-Sequence-Number: 3765 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 =20 clause is one row. E.g. like this: $res =3D pg_fetch_row(pg_query("SELECT COUNT(*) AS result_count FROM =20 etc. WHERE etc.")); //Not a very good style :-), one should check if =20 the query runs $count =3D $res[0]; Another way to calculate number of rows in the result is to use =20 pg_num_rows function. $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc."); $count =3D pg_num_rows($res); 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 coun= t > 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 f= or > a query that in actuality produces three results). > > I tried using an AS clause (SELECT COUNT(*) AS result_count FROM etc. WHER= E > 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] =3D> Array > ( > [result_count] =3D> 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 tryin= g > 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 >