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