Re: Retrieving result of COUNT(*) with PHP

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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@xxxxxxxxxxxxxx>:

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!





[Index of Archives]     [Postgresql General]     [Postgresql Admin]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Yosemite Backpacking]     [Postgresql Jobs]

  Powered by Linux