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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Retrieving result of COUNT(*) with PHP

------=_Part_42061_7162533.1175114916586
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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 . "
\n";

Should do the trick.

Hope this helps,

Gavin


On 3/28/07, Lynna Landstreet 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
>

------=_Part_42061_7162533.1175114916586
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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

Re: Retrieving result of COUNT(*) with PHP

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"


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Retrieving result of COUNT(*) with PHP

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 =3D 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 =3D $res[0];

Another way to calculate number of rows in the result is to use
pg_num_rows function.
$res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.");
$count =3D pg_num_rows($res);

Enjoy!

Mihail



Quoting Lynna Landstreet :

> 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 cou=
nt
> 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. WHE=
RE
> 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] =3D> Array
> (
> [result_count] =3D> 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 tryi=
ng
> 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

Re: Retrieving result of COUNT(*) with PHP

------=_Part_42385_27467695.1175116714960
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 3/28/07, Mihail Mihailov wrote:
>
>
> 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);


I dont think this will work as expected. PostgreSQL will return 1 row and
your $count var will be 1. If you do SELECT * FROM it would return the
proper result. Not very effective for speed though.

http://www.varlena.com/GeneralBits/120.php has some good suggestions for
dealing with count(*) speed issues.

Gavin

------=_Part_42385_27467695.1175116714960
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Re: Retrieving result of COUNT(*) with PHP

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 FROM etc" then call
pg_num_rows.
Although I've never had problems with COUNT(*) working.

Perhaps try doing SELECT COUNT() as num_of_rows FROM
etc?

Andy.

>
> Enjoy!
>
> Mihail
>
>
>
> Quoting Lynna Landstreet :
>
>> 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

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

Re: Retrieving result of COUNT(*) with PHP

------=_Part_20910_12533873.1175117498705
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

$sql =3D "select * from TableName";
$count =3D pg_query($sql);
$count =3D pg_num_rows($count);
?>


I thing that's enough :)

2007/3/28, Mihail Mihailov :
>
> 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 =3D 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 =3D $res[0];
>
> Another way to calculate number of rows in the result is to use
> pg_num_rows function.
> $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.")=
;
> $count =3D pg_num_rows($res);
>
> Enjoy!
>
> Mihail
>
>
>
> Quoting Lynna Landstreet :
>
> > 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,60=
8
> 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] =3D> Array
> > (
> > [result_count] =3D> 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
>



--=20
************************************************************ ***************
Ministerio de Planificaci=F3n y Desarrollo de la Rep=FAblica Bolivariana de
Venezuela
Direcci=F3n de Planes de Personal

Jos=E9 Valent=EDn Orfila Brice=F1o
Programador II

Tel=E9fonos: Celular: (0416) 4131418

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

Messenger: valentinorfila@hotmail.com

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

------=_Part_20910_12533873.1175117498705
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline


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

<?=

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


I thing that's enough :)

Re: Retrieving result of COUNT(*) with PHP

------=_Part_42585_27477153.1175117903506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 =3D microtime(true);
$result =3D pg_Exec($conn, "SELECT i_poemid FROM poetry;");
$rows =3D pg_NumRows($result);
$end =3D microtime(true) - $start;

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

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

Regards,

Gavin




On 3/28/07, Valent=EDn Orfila wrote:
>
>
> Where people this the way I do, count (*) could be slower
>
> > $sql =3D "select * from TableName";
> $count =3D pg_query($sql);
> $count =3D pg_num_rows($count);
> ?>
>
>
> I thing that's enough :)
>
> 2007/3/28, Mihail Mihailov :
> >
> > 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 =3D 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 =3D $res[0];
> >
> > Another way to calculate number of rows in the result is to use
> > pg_num_rows function.
> > $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.=
");
> >
> > $count =3D pg_num_rows($res);
> >
> > Enjoy!
> >
> > Mihail
> >
> >
> >
> > Quoting Lynna Landstreet :
> >
> > > Hi there,
> > >
> > > I'm trying to use a SELECT COUNT(*) to count how many results would b=
e
> >
> > > 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] =3D> Array
> > > (
> > > [result_count] =3D> 1714608
> > > )
> > >
> > > )
> > >
> > > Again with the weird number. And yet, if I run the exact same query i=
n
> > 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 countin=
g
> > 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
> >
>
>
>
> --
> ************************************************************ *************=
**
>
> Ministerio de Planificaci=F3n y Desarrollo de la Rep=FAblica Bolivariana =
de
> Venezuela
> Direcci=F3n de Planes de Personal
>
> Jos=E9 Valent=EDn Orfila Brice=F1o
> Programador II
>
> Tel=E9fonos: Celular: (0416) 4131418
>
> E-mail: valentinorfila@gmail.com
> valentinorfila@hotmail.com
> josevalentinorfila@yahoo.com
>
> Messenger: valentinorfila@hotmail.com
>
> Skype: valentinorfila
> valentinorfila@gmail.com
> ************************************************************ *************=
**
>

------=_Part_42585_27477153.1175117903506
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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: 6=
0348 rows returned, duration of=20
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=
=20
0.110275 seconds

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

Source Code:

  // pg_NumRows c=
ode

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

  // Cou=
nt Code
  $start =3D microtime(true);

  $result =3D pg_Exec($conn, "SELECT count(*) FROM poetry;&qu=
ot;);
  $data =3D pg_Fetch_Object($result, 0);
  $send =3D =
microtime(true) - $start;

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


Regards,

Gavin

Re: Retrieving result of COUNT(*) with PHP

------=_Part_42597_26523161.1175117926536
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

On 3/28/07, Gavin M. Roy wrote:
>
> For large volumes of data, this will be slower. PostgreSQL has to do mor=
e
> 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 milisecond=
s
> Run 3, with count: 60348 count returned, duration of 0.061281 miliseconds
>
> Source Code:
>
> // pg_NumRows code
> $start =3D microtime(true);
> $result =3D pg_Exec($conn, "SELECT i_poemid FROM poetry;");
> $rows =3D pg_NumRows($result);
> $end =3D microtime(true) - $start;
>
> // Count Code
> $start =3D microtime(true);
> $result =3D pg_Exec($conn, "SELECT count(*) FROM poetry;");
> $data =3D pg_Fetch_Object($result, 0);
> $send =3D microtime(true) - $start;
>
> In this scenario, i_poemid is the primary key of a table with 60k rows.
>
> Regards,
>
> Gavin
>
>
>
>
> On 3/28/07, Valent=EDn Orfila wrote:
> >
> >
> > Where people this the way I do, count (*) could be slower
> >
> > > > $sql =3D "select * from TableName";
> > $count =3D pg_query($sql);
> > $count =3D pg_num_rows($count);
> > ?>
> >
> >
> > I thing that's enough :)
> >
> > 2007/3/28, Mihail Mihailov :
> > >
> > > 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 =3D 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 =3D $res[0];
> > >
> > > Another way to calculate number of rows in the result is to use
> > > pg_num_rows function.
> > > $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE
> > > etc.");
> > > $count =3D pg_num_rows($res);
> > >
> > > Enjoy!
> > >
> > > Mihail
> > >
> > >
> > >
> > > Quoting Lynna Landstreet :
> > >
> > > > 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 kin=
d
> > > 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] =3D> Array
> > > > (
> > > > [result_count] =3D> 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 wa=
s
> > > 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
> > >
> >
> >
> >
> > --
> > ************************************************************ ***********=
****
> >
> > Ministerio de Planificaci=F3n y Desarrollo de la Rep=FAblica Bolivarian=
a de
> > Venezuela
> > Direcci=F3n de Planes de Personal
> >
> > Jos=E9 Valent=EDn Orfila Brice=F1o
> > Programador II
> >
> > Tel=E9fonos: Celular: (0416) 4131418
> >
> > E-mail: valentinorfila@gmail.com
> > valentinorfila@hotmail.com
> > josevalentinorfila@yahoo.com
> >
> > Messenger: valentinorfila@hotmail.com
> >
> > Skype: valentinorfila
> > valentinorfila@gmail.com
> > ************************************************************ ***********=
****
> >
>
>
>

------=_Part_42597_26523161.1175117926536
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

Re: Retrieving result of COUNT(*) with PHP

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

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

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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Retrieving result of COUNT(*) with PHP

On 3/28/07 5:21 PM, Bill Moran 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Re: Retrieving result of COUNT(*) with PHP

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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 =3D pg_query("SELECT * AS result_count FROM etc. WHERE etc.");
$count =3D pg_num_rows($res);

M.


>> Another way to calculate number of rows in the result is to use =20
>> pg_num_rows function.
>> $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc."=
);
>> $count =3D 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 FROM etc" then call
> pg_num_rows.
> Although I've never had problems with COUNT(*) working.
>
> Perhaps try doing SELECT COUNT() as num_of_rows FROM =
etc?
>
> Andy.
>
>>
>> Enjoy!
>>
>> Mihail
>>
>>
>>
>> Quoting Lynna Landstreet :
>>
>>> 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 c=
ount
>>> in PHP. The result on its own is a resource rather than a specific valu=
e,
>>> 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 resembla=
nce
>>> to the number of results the query actually gets when it runs =20
>>> (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. W=
HERE
>>> 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] =3D> Array
>>> (
>>> [result_count] =3D> 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 pag=
e's
>>> worth of results, but it seemed wasteful to do it that way, so I was tr=
ying
>>> to do it more efficiently... :-/
>>>
>>> Thanks,
>>>
>>> Lynna
>>>
>>> --=20
>>> 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
>


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Retrieving result of COUNT(*) with PHP

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend