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!
