Welcome to WebmasterWorld Guest from 54.147.44.93

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Popular Categories Query

     

woldie

11:48 am on Jan 20, 2006 (gmt 0)

10+ Year Member



Hi,

What I am trying to achieve is to create a popular categories functionality on the site whereby I would like to list the top 6 categories according to the number of stories submitted for each of the categories.

So basically I have around 20 categories in all but I would like to display the top 6 and then a link to the rest of the categories. Now I've made start on this but I've been scratching my head on this one, and just need pointing in the right direction.

The code below shows that I get each total for each category, and place that in the array with the ncid for that category because I will need to query the categories table to get the category name, if you get what I mean.

Anyway here's the code:

<?
// TABLE OF CATEGORIES

$result=mysql_query("select ncid
from tbl_news_categories");
while (list($DBncid)=mysql_fetch_row($result))
{

// QUERY TO COUNT THE NUMBER OF NEWS STORIES FOR EACH CATEGORY

$sql_total=mysql_query("select count(*) as total,tbl_news_categories.ncid,news_catname
from tbl_news,tbl_news_categories
where tbl_news.ncid=tbl_news_categories.ncid
and tbl_news_categories.ncid=$DBncid
group by tbl_news_categories.ncid
order by total desc");
list($DBtotal,$DBncid_2,$DBcat_name)=mysql_fetch_row($sql_total);

// CREATE ARRAY TO STORE TOTAL FOR EACH CATEGORY - DON'T KNOW IF THIS IS THE RIGHT WAY OR NOT?

$ncids[$DBtotal]=$DBncid_2;

#$totals[$DBcat_name]=$DBtotal;
#$cat_names[]=$DBcat_name;
}

// TEST TO DISPLAY THE ARRAY

foreach ($ncids as $key => $val)
echo "$key = $val<br>";


?>

Any help always appreciated.

W. :o)

newbie2006

12:17 am on Jan 22, 2006 (gmt 0)

5+ Year Member



$sql_total=mysql_query("select count(*) as total,tbl_news_categories.ncid,news_catname
from tbl_news,tbl_news_categories
where tbl_news.ncid=tbl_news_categories.ncid
and tbl_news_categories.ncid=$DBncid
group by tbl_news_categories.ncid
order by total desc");

If you do not need to get all results add
LIMIT 6 in the end, so it will be like:

order by total desc LIMIT 6");

Not sure if it is what you are looking, let me know please if it helps or not.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month