pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andy Shellam <andy.shellam-lists@mailnetwork.co.uk>
To: pgsql-php@postgresql.org
Subject: Re: Retrieving result of COUNT(*) with PHP
Date: Wed, 28 Mar 2007 22:19:25 +0100
Message-ID: <460ADBDD.5020205@mailnetwork.co.uk> (raw)
In-Reply-To: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi>
References: <C2303737.4AFAD%lynna@spidersilk.net>
	<20070329000511.qidyijwozuo0w0w4@imp3.uta.fi>

See note below...

Mihail Mihailov wrote:
> 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 
> clause is one row.
>
> E.g. like this:
> $res = pg_fetch_row(pg_query("SELECT COUNT(*) AS result_count FROM 
> etc. WHERE etc.")); //Not a very good style :-), one should check if 
> the query runs
> $count = $res[0];
>
> Another way to calculate number of rows in the result is to use 
> pg_num_rows function.
> $res = pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.");
> $count = 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 <primary key column> FROM etc" then call 
pg_num_rows.
Although I've never had problems with COUNT(*) working.

Perhaps try doing SELECT COUNT(<primary key column>) as num_of_rows FROM 
etc?

Andy.

>
> Enjoy!
>
> Mihail
>
>
>
> Quoting Lynna Landstreet <lynna@spidersilk.net>:
>
>> 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
>>
>
>
> ---------------------------(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!
>
>




view thread (15+ messages)  latest in thread

Message-ID: <460ADBDD.5020205@mailnetwork.co.uk>
Permalink:  ../460ADBDD.5020205@mailnetwork.co.uk/
Also on:    postgresql.org/message-id/460ADBDD.5020205@mailnetwork.co.uk

 · 

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: andy.shellam-lists@mailnetwork.co.uk
  Subject: Re: Retrieving result of COUNT(*) with PHP
  In-Reply-To: <460ADBDD.5020205@mailnetwork.co.uk>
  References: <C2303737.4AFAD%lynna@spidersilk.net> <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> <460ADBDD.5020205@mailnetwork.co.uk>

* 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