Re: Retrieving result of COUNT(*) with PHP

Of course you are right. I just copied the first script and forgot to
change it.
You do not need count function for the second solution. It should be
like this:

$res = pg_query("SELECT * AS result_count FROM etc. WHERE etc.");
$count = pg_num_rows($res);

M.


>> 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!
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>