Better way to get count

[Lionel]

What is a better way to get the count in below query? The code in the foreach loop is killing me. I am trying to get the number of threads with same threadprefix

$getcount=$db->query_first("SELECT count(*) as tot from ".TABLE_PREFIX."thread WHERE threadprefix='$threadprefix'");
$tot = number_format($getcount['tot']);

PHP Code:

$getprefixes=$db->query_read("SELECT forumid,threadprefix from ".TABLE_PREFIX."forum 
WHERE forumid='276'");
$color1 = "alt1";  
$color2 = "alt2";
$row_count = 0;
WHILE($foruminfo=$db->fetch_array($getprefixes)) {
$threadprefixes = explode("\r\n", unhtmlspecialchars($foruminfo['threadprefix']));
foreach ($threadprefixes as $threadprefix)
{
$getcount=$db->query_first("SELECT count(*) as tot from ".TABLE_PREFIX."thread WHERE threadprefix='$threadprefix'");
$tot = number_format($getcount['tot']);
if(!empty($threadprefix)){
$forumid=$foruminfo['forumid'];
$moviecat="<a href=\"forumdisplay.php?s=&f=$forumid&threadprefix=$threadprefix\">".$threadprefix."</a>";
eval('$movies .= "' . fetch_template('hw_catcount') . '";');
}
} 


[Cap'n Steve]

I think you want to look into the "GROUP BY" syntax. Try somthing like this:

PHP Code:

$getprefixes = $db->query_read('SELECT count(*) as tot from ' . TABLE_PREFIX . 'thread AS thread
GROUP BY thread.threadprefix'); 


[Lionel]

Thanks, but still no luck

Looks like there is no other way to get that count. Unfortunately this adds a lot of queries when in foreach loop and page takes a long time to load.