pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gavin M. Roy <gmr@ehpg.net>
To: Lynna Landstreet <lynna@spidersilk.net>
Cc: pgsql-php@postgresql.org
Subject: Re: Retrieving result of COUNT(*) with PHP
Date: Wed, 28 Mar 2007 13:48:36 -0700
Message-ID: <5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com> (raw)
In-Reply-To: <C2303737.4AFAD%lynna@spidersilk.net>
References: <C2303737.4AFAD%lynna@spidersilk.net>

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
>

view thread (15+ messages)  latest in thread

Message-ID: <5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com>
Permalink:  ../5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com/
Also on:    postgresql.org/message-id/5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com

 · 

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: gmr@ehpg.net, lynna@spidersilk.net
  Subject: Re: Retrieving result of COUNT(*) with PHP
  In-Reply-To: <5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com>
  References: <C2303737.4AFAD%lynna@spidersilk.net> <5b599cc10703281348k6deb6952m3a31d5f1578c5daf@mail.gmail.com>

* 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