Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5386E9FBA02 for ; Wed, 28 Mar 2007 17:48:49 -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 03471-06 for ; Wed, 28 Mar 2007 17:48:36 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.191]) by postgresql.org (Postfix) with ESMTP id D24579FB4C5 for ; Wed, 28 Mar 2007 17:48:38 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id x4so3796726nfb for ; Wed, 28 Mar 2007 13:48:37 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=NEJ4nSKDJ0ac4ozxifZC5l9Lf/EdAzbx+ofxJi3FmYVzxL+/LqddSithYgI5ERFfXm+B+vb2aElKYXPC2hPtj/CSCZTEtwfZCYlqGiv+IlsFIlkAvWpRFaxesCy4ukQ9etf2p20aPf20jDPX+Y+4R45tY2Mt88ocQR0IZzr4sFk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=dyf3nCBYvO71ze/iL2HY/z0hSnbZr/RmEiF2V+svdaI5UJOs/xfRjIl2yD8ZtN0whxuJOWZj4cYXOjOnRNRzg7gUPmYGz0k147k8yrmXhg8/CnEkgkJ1SFejmnMiWYu0lKUXcFk03rsKJWO3p6orHhfsZOc4qyR/wv6jCzS72ZI= Received: by 10.82.134.12 with SMTP id h12mr19734872bud.1175114916712; Wed, 28 Mar 2007 13:48:36 -0700 (PDT) Received: by 10.82.114.5 with HTTP; Wed, 28 Mar 2007 13:48:36 -0700 (PDT) Message-ID: <5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com> Date: Wed, 28 Mar 2007 13:48:36 -0700 From: "Gavin M. Roy" To: "Lynna Landstreet" Subject: Re: Retrieving result of COUNT(*) with PHP Cc: pgsql-php@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_42061_7162533.1175114916586" References: X-Google-Sender-Auth: 768bc1a96ec459bd X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/52 X-Sequence-Number: 3764 ------=_Part_42061_7162533.1175114916586 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline It will return the result as the column name "count" unless you specify it as something else: SELECT count(*) AS numrows FROM mytable; Let me caution you that SELECT count(*) is slow on larger tables... But as for your example: $result = pg_Query($conn, 'SELECT count(*) FROM table;'); $data = pg_Fetch_Object($result, 0); echo 'My Count is: ' . $data->count . "
\n"; Should do the trick. Hope this helps, Gavin On 3/28/07, Lynna Landstreet wrote: > > 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 > ------=_Part_42061_7162533.1175114916586 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline It will return the result as the column name "count" unless you specify it as something else:

SELECT count(*) AS numrows FROM mytable;

Let me caution you that SELECT count(*) is slow on larger tables...

But as for your example:

  $result = pg_Query($conn, 'SELECT count(*) FROM table;');
  $data = pg_Fetch_Object($result, 0);
  echo 'My Count is: ' . $data->count . "<br />\n";

Should do the trick.

Hope this helps,

Gavin


On 3/28/07, Lynna Landstreet <lynna@spidersilk.net > wrote:
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

------=_Part_42061_7162533.1175114916586--