homepage Welcome to WebmasterWorld Guest from 54.197.147.90
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Popular Categories Query
woldie




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved