Thread: Retrieving result of COUNT(*) with PHP

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

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

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);

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
>

At 03:16 PM 3/28/07, Lynna Landstreet wrote:
>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.


Why not simply run your query and then call pg_numrows( )  ?

You didn't show us any php code - this works..

   $res = pg_query( $dbc, 'SELECT COUNT(*)  FROM payperiod');
   $val = pg_fetch_result( $res, 0, 0 );
   var_dump ($val );

might output something like

string(3) "205"

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@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!
>
>

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 <lynna@spidersilk.net>:
>
> 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

Where people this the way I do, count (*) could be slower

<?
$sql = "select * from TableName";
$count = pg_query($sql);   
$count = pg_num_rows($count);
?>
<td ><?=$count?></td>

I thing that's enough :)




--
***************************************************************************
Ministerio de Planificación y Desarrollo de la República Bolivariana de Venezuela
Dirección de Planes de Personal

José Valentín Orfila Briceño
Programador II

Teléfonos: Celular: (0416) 4131418

E-mail: valentinorfila@gmail.com
           valentinorfila@hotmail.com
           josevalentinorfila@yahoo.com

Messenger: valentinorfila@hotmail.com

Skype: valentinorfila
           valentinorfila@gmail.com
***************************************************************************

For large volumes of data, this will be slower.  PostgreSQL has to do more work on select * FROM table than select count(*) from table...

I wrote a little test script to explain this:

Run 1, with numRows: 60348 rows returned, duration of 0.379431 seconds
Run 1, with count: 60348 count returned, duration of 0.061221 seconds

Run 2, with numRows: 60348 rows returned, duration of 0.323478 seconds
Run 2, with count: 60348 count returned, duration of 0.110275 seconds

Run 3, with numRows: 60348 rows returned, duration of 0.379429 miliseconds
Run 3, with count: 60348 count returned, duration of 0.061281 miliseconds

Source Code:

  // pg_NumRows code
  $start = microtime(true);
  $result = pg_Exec($conn, "SELECT i_poemid FROM poetry;");
  $rows = pg_NumRows($result);
  $end = microtime(true) - $start;

  // Count Code
  $start = microtime(true);
  $result = pg_Exec($conn, "SELECT count(*) FROM poetry;");
  $data = pg_Fetch_Object($result, 0);
  $send = microtime(true) - $start;

In this scenario, i_poemid is the primary key of a table with 60k rows.

Regards,

Gavin

Pardon the label on the last run, it should be seconds.

On Wednesday 28 March 2007 14:31, "Valentín Orfila"
<valentinorfila@gmail.com> wrote:
> Where people this the way I do, count (*) could be slower
>
> <?
> $sql = "select * from TableName";
> $count = pg_query($sql);
> $count = pg_num_rows($count);
> ?>
> <td ><?=$count?></td>
>
> I thing that's enough :)

Apparently you have no tables with any real amount of data in them.

--
"It is a besetting vice of democracies to substitute public opinion for
law." - James Fenimore Cooper

On 3/28/07 5:21 PM, Bill Moran <wmoran@potentialtech.com> wrote:

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

Hmm, OK.

The code is a bit lengthy because it has to allow for three different search
modes (any, all and exact phrase). So first I define the first part of the
search query as:

$searchquery = "SELECT i.image_id, i.title, i.medium, j.artist_id,
    a.firstname, a.lastname
    FROM images i, art_img_join j, artists a
    WHERE (i.image_id = j.image_id
    AND j.artist_id = a.artist_id)
    AND ";

And then depending on what type of search they chose it does any of three
different things with their search text. In the case of an "any" search (as
in, any of the words they entered), it does this:

// break into an array of separate words, count them

$searchwords = explode (" ", $searchtext);
$wordcount = count ($searchwords);

// loop through array adding each word to select

foreach ($searchwords as $key => $word) {

    $where_clause .= "i.title ILIKE '%$word%'
        OR i.series ILIKE '%$word%'
        OR i.medium ILIKE '%$word%'";

    if (($key + 1) < $wordcount) {
        $where_clause .= " OR ";
    }
}

And then, for any of the three types, it adds this:

$searchquery .= $where_clause . " ORDER BY lower(i.title)";

That's for the actual search query. Now, the reason I didn't just run the
query and use pg_num_rows is because the images table has quite a lot of
records. If someone searches for a fairly common word or phrase, they could
get 20 pages of results... So I didn't want to put any more stress on the
database, or slow things down any more, than I had to. Thus, I thought the
count(*) approach might be more efficient.

So what I did with that was this:

$count_query = "SELECT COUNT(*) AS result_count
    FROM images i, art_img_join j, artists a
    WHERE " . $where_clause;

(I didn't originally have the "AS result_count" in there - I added that when
I was having trouble extracting the value, hoping that giving it a more
specific name might help. It didn't.)

$result_count = pg_query($count_query);

$numrows = [any number of things I've tried]

The $numrows variable is used by the paging script I'm using, which I
adapted from one from the PHP Resource Index, to calculate the number of
pages needed, offset, etc.

I've lost track of how many ways I've tried to get the count value into
$numrows, but I know that I've tried, among other things:

pg_fetch_result($result_count, 0, 'count');
pg_fetch_result($result_count, 0, 'result_count');
(I tried this one after adding the AS clause to the query)
pg_fetch_result($result_count, 0);
pg_fetch_result($result_count);

And none of these worked.

I only tried pg_fetch_all so that I could get some idea of what data was
actually in the resource and how it was arranged. I know I don't need a
multidimensional array to get one value. :-) But it didn't help much except
to show that the strange number I was getting was actually in there, despite
the fact that the search query itself worked fine and brought up the number
of results you would think it would.

The system specs are:

Server: FreBSD 6.2
PHP: 4.4.2 (with Suhosin Patch 0.9.6)
PostgreSQL: 7.4.14


Lynna


>> 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... :-/


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

> The code is a bit lengthy because it has to allow for three different search
> modes (any, all and exact phrase). So first I define the first part of the
> search query as:
>
> $searchquery = "SELECT i.image_id, i.title, i.medium, j.artist_id,
>     a.firstname, a.lastname
>     FROM images i, art_img_join j, artists a
>     WHERE (i.image_id = j.image_id
>     AND j.artist_id = a.artist_id)
>     AND ";
>
> And then depending on what type of search they chose it does any of three
> different things with their search text. In the case of an "any" search (as
> in, any of the words they entered), it does this:
>
> // break into an array of separate words, count them
>
> $searchwords = explode (" ", $searchtext);
> $wordcount = count ($searchwords);
>
> // loop through array adding each word to select
>
> foreach ($searchwords as $key => $word) {
>
>     $where_clause .= "i.title ILIKE '%$word%'
>         OR i.series ILIKE '%$word%'
>         OR i.medium ILIKE '%$word%'";
>
>     if (($key + 1) < $wordcount) {
>         $where_clause .= " OR ";
>     }
> }
>
> And then, for any of the three types, it adds this:
>
> $searchquery .= $where_clause . " ORDER BY lower(i.title)";
>
> That's for the actual search query. Now, the reason I didn't just run the
> query and use pg_num_rows is because the images table has quite a lot of
> records. If someone searches for a fairly common word or phrase, they could
> get 20 pages of results... So I didn't want to put any more stress on the
> database, or slow things down any more, than I had to. Thus, I thought the
> count(*) approach might be more efficient.

Don't do pg_num_rows - it's the wrong approach for this problem.

> So what I did with that was this:
>
> $count_query = "SELECT COUNT(*) AS result_count
>     FROM images i, art_img_join j, artists a
>     WHERE " . $where_clause;
>
> (I didn't originally have the "AS result_count" in there - I added that when
> I was having trouble extracting the value, hoping that giving it a more
> specific name might help. It didn't.)
>
> $result_count = pg_query($count_query);
>
> $numrows = [any number of things I've tried]

$count_row = pg_fetch_assoc($result_count) or die ('problem: ' .
pg_last_error());
$numrows = $count_row['result_count'];

:D

You can't do it all in one step with pg_fetch_assoc (afaik), but this is
easy enough.

No idea why it's not working with pg_fetch_result but this way does work.

--
Postgresql & php tutorials
http://www.designmagick.com/

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
>

Hi there,

OK, in tinkering around with this further, I seem to have solved it, and
everything is working now - thought I'm still not 100% sure why it wasn't
before!

I did find an error in the count_query string at one point that would have
accounted for it bringing up a different result than the actual number of
results the search query brought in - but I'm reasonably sure at some point
during this I had copied and pasted the count query into phpPgAdmin and
gotten a result that was consistent with the search query, but then got a
different result in my PHP script. Still not sure how that happened...

But anyway, the important part is it's working now. Thanks to everyone who
replied!


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