SQL for counting comments - is this smart?

Is it smart to use all of this on one page?
Or should I rather do one SQL and let PHP count it?


$q = $DB->q("SELECT COUNT(*) FROM comments");
$int_total = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
$int_waiting = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
$int_deleted = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
$int_approved = $DB->frow($q);

$q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
$int_banned = $DB->frow($q);


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL for counting comments - is this smart?

Martin Zvarík wrote:
> Is it smart to use all of this on one page?
> Or should I rather do one SQL and let PHP count it?
>
>
> $q = $DB->q("SELECT COUNT(*) FROM comments");
> $int_total = $DB->frow($q);
>
> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
> $int_waiting = $DB->frow($q);
>
> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=0");
> $int_deleted = $DB->frow($q);
>
> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=1");
> $int_approved = $DB->frow($q);
>
> $q = $DB->q("SELECT COUNT(*) FROM comments WHERE approved=2");
> $int_banned = $DB->frow($q);

Each one of these probably going to scan the whole table because the
approved column isn't going to be selective enough to use an index.

You might be better off doing:

select approved, count(*) from comments group by approved;

then in php separating them out:

while ($row = $DB->frow($q)) {
switch ($row['approved']) {
case null:
$waiting = $row['count'];
break;
case 0:
$deleted = $row['count'];
break;
case 1:
$approved = $row['count'];
break;
}
}

$total = $waiting + $approved + $deleted;

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


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: SQL for counting comments - is this smart?

--0016e64ec0ca4bb4f50465515b8c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Chris your answer is the better solution, I thinked that the option suggest
by Martin was fine.

anywhere, tanks for help us to improve us code.

Gerardo.

2009/3/16 Chris

> Martin Zvar=EDk wrote:
>
>> Is it smart to use all of this on one page?
>> Or should I rather do one SQL and let PHP count it?
>>
>>
>> $q =3D $DB->q("SELECT COUNT(*) FROM comments");
>> $int_total =3D $DB->frow($q);
>>
>> $q =3D $DB->q("SELECT COUNT(*) FROM comments WHERE approved IS NULL");
>> $int_waiting =3D $DB->frow($q);
>>
>> $q =3D $DB->q("SELECT COUNT(*) FROM comments WHERE approved=3D0");
>> $int_deleted =3D $DB->frow($q);
>>
>> $q =3D $DB->q("SELECT COUNT(*) FROM comments WHERE approved=3D1");
>> $int_approved =3D $DB->frow($q);
>>
>> $q =3D $DB->q("SELECT COUNT(*) FROM comments WHERE approved=3D2");
>> $int_banned =3D $DB->frow($q);
>>
>
> Each one of these probably going to scan the whole table because the
> approved column isn't going to be selective enough to use an index.
>
> You might be better off doing:
>
> select approved, count(*) from comments group by approved;
>
> then in php separating them out:
>
> while ($row =3D $DB->frow($q)) {
> switch ($row['approved']) {
> case null:
> $waiting =3D $row['count'];
> break;
> case 0:
> $deleted =3D $row['count'];
> break;
> case 1:
> $approved =3D $row['count'];
> break;
> }
> }
>
> $total =3D $waiting + $approved + $deleted;
>
> --
> Postgresql & php tutorials
> http://www.designmagick.com/
>
>
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

--0016e64ec0ca4bb4f50465515b8c--