Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id AB0F49FB21E for ; Thu, 29 Mar 2007 07:00:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 61097-05 for ; Thu, 29 Mar 2007 07:00:42 -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 1819F9FB69A for ; Thu, 29 Mar 2007 07:00:41 -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 l2TA08Ko006298 for ; Thu, 29 Mar 2007 13:00:40 +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 l2T9n7Uo023524 for ; Thu, 29 Mar 2007 12:49:08 +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 l2T9n7RA021521 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NOT) for ; Thu, 29 Mar 2007 12:49:07 +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 l2T9n7qE027061 for ; Thu, 29 Mar 2007 12:49:07 +0300 Received: (from wwwroot@localhost) by pippuri.uta.fi (8.13.4/8.13.4/Submit) id l2T9n7Hu027059 for pgsql-php@postgresql.org; Thu, 29 Mar 2007 12:49:07 +0300 Received: from kielitranpc59.uta.fi (kielitranpc59.uta.fi [153.1.35.59]) by imp3.uta.fi (Horde MIME library) with HTTP; Thu, 29 Mar 2007 12:49:07 +0300 Message-ID: <20070329124907.r01lf0vmv0lc48kg@imp3.uta.fi> Date: Thu, 29 Mar 2007 12:49:07 +0300 From: Mihail Mihailov To: pgsql-php@postgresql.org Subject: Re: Retrieving result of COUNT(*) with PHP References: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> <460ADBDD.5020205@mailnetwork.co.uk> In-Reply-To: <460ADBDD.5020205@mailnetwork.co.uk> 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 12:49:07 +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/64 X-Sequence-Number: 3776 Of course you are right. I just copied the first script and forgot to =20 change it. You do not need count function for the second solution. It should be =20 like this: $res =3D pg_query("SELECT * AS result_count FROM etc. WHERE etc."); $count =3D pg_num_rows($res); M. >> 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); > > ^^ 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 e= tc? > > 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 co= unt >>> 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 resemblan= ce >>> to the number of results the query actually gets when it runs =20 >>> (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. WH= ERE >>> 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 t= he >>> 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 t= he >>> 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 try= ing >>> to do it more efficiently... :-/ >>> >>> Thanks, >>> >>> Lynna >>> >>> --=20 >>> 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! >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >