Re: [PHP-DB] SQL for counting comments - is this smart?

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 <[email protected]>

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