pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Lynna Landstreet <lynna@spidersilk.net>
To: pgsql-php@postgresql.org
Subject: Retrieving result of COUNT(*) with PHP
Date: Wed, 28 Mar 2007 15:16:07 -0400
Message-ID: <C2303737.4AFAD%lynna@spidersilk.net> (raw)

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



view thread (15+ messages)  latest in thread

Message-ID: <C2303737.4AFAD%lynna@spidersilk.net>
Permalink:  ../C2303737.4AFAD%25lynna@spidersilk.net/
Also on:    postgresql.org/message-id/C2303737.4AFAD%lynna@spidersilk.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: lynna@spidersilk.net
  Subject: Re: Retrieving result of COUNT(*) with PHP
  In-Reply-To: <C2303737.4AFAD%lynna@spidersilk.net>
  References: <C2303737.4AFAD%lynna@spidersilk.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox