Re: Retrieving result of COUNT(*) with PHP

The upshot of what a lot of other people are saying is, "what you are trying
to do should work."

Your results are atypical.  Despite the number of answers that suggest you
try a slightly different approach, the way you're doing it should work, I've
used it without problem.

I would recommend you provide an actual code sample, to ensure you're not
making some minor mistake.  If it turns out that this doesn't work on your
setup, I would suggest that it's probably a PHP problem and not a Postgres
one.  More specifically, it's probably a problem with the specific version/
packaging/installation/OS of your PHP, as I've never seen the type of
behaviour you're describing.

In response to Lynna Landstreet <[email protected]>:
> 
> 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


-- 
Bill Moran
http://www.potentialtech.com

---------------------------(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