how would i go about putting the top 5 links of each category on the first page.. i know everything except for how to make this prepare statement..

right now.. i don't see how to do this WITH category alternatives without giving the same link twice.. so that doesn't matter at all to me..

but what i am trying to get is for it to work with the categories subcategories..

using CategoryHeiarchy of course..

SELECT Links.*
FROM Links, CategoryHeiarchy
WHERE Links.CategoryID = ? OR
CategoryHeiarchy.SubCategoryID = Links.CategoryID
ORDER BY Hits DESC
LIMIT 5

any ideas?

jerry

Hmm, I'm not sure you could do it in one query. You might just have to do a SELECT ID, Name from Category, and then loop through each category and pull out the top 5.

Cheers,

Alex

yep.. yesterday i tried and got 2 sets of code.. one for the category (easy and then one for it's subcategories.. but you can't combine them..

what i am going to probably end up doing is just splitting Software into it's own database and then i wouldn't even have to care about the category

SELECT * FROM Software ORDER BY Hits DESC LIMIT 5

i've got this working (sorta).. but i think i have left out a BUNCH of code..

anyways..
jerry

Oh, I misunderstood. You want the top 5 links for each of your root level categories (including subcats)?

It will still take more then one query but:

1. SELECT CategoryID FROM CategoryHeiarchy WHERE SubCategoryID = 0; # Get's a list of all root level categories.

2. Foreach of the above do:

3. SELECT SubCategoryID FROM CategoryHeiarchry WHERE CategoryID = ? # Where ? is the current categoryid you are working on.

4. Join all the categoryid's you get from query #3 and do: SELECT * FROM Links WHERE CategoryID IN (yourlist) ORDER BY Hits DESC LIMIT 5. # This gets the top 5 links in the root category you are working on.

Hope this helps,

Alex

hehe.. ok i get it now.. but i was looking for more "CONVEINENCE"

hehe.. that third query is going to be ONE LONG ONE.. but oh well.. at least i'm not the mySQL server.. hehe :P

thanks!
jerry

ok.. this is what i did:


it works great.. but i am looking for suggestions..

of course you send top5 through the line a few lines down..

jerry

Where would you insert this? nph_build or HTML_templates ?

nph-build.cgi..

it's top 5 for one category+it's subcategories fyi.. similiar to the one i made for links 2..

i don't really care about top5 for whole thing.. cause i rather not mix hardware and software up together..

who downloads hardware?

jerry

Instead of long OR lists, you can use the IN operator.

Basically, convert your list of Subcategory ID's to a list of comma separated ints. Then do:

SELECT * FROM Links WHERE CategoryID IN (list) ORDER BY Hits DESC LIMIT 5

Where list would look like:

5,192,13,284,182

or some comma list of categories. It's meant to be much more efficent in SQL this way.

Cheers,

Alex

i need to learn more mysql commands..

jerry

This is a great mod that could be generalized to the next release of Links. Add a field to the Category table "Show Top" and a default value can be set in the Links.pm file, so that if the category has the "Show Top" set the program looks for the subcat_top.html template and inserts the top N links after it.

The same could be for the "Top Cat" -- show the most popular (categories with the most agregate hits) rather than links.

The logic modification to links would be fairly small (I think - check for "Top Cat" exisitance, and insert any found matches) but the possibilities would be quite endless

This is one great example how increased modularity would allow advaced feature programming.

What should i change if I want the Top 5 just of the main categories, and not all of it´s subcategories?

Thanks

Hey widgetz, can you please, point me what to change to just have the top 5 of root categories?

thanks...

Well, first of all, I need to know exactly where to put this code, or if I need to make a sub to put this, where to call it..

And what was your final code, as Alex made a suggestion and I am not good in SQL....

Thanks

the code above isn't even right

anyways.. to do all categories the query is just

select * from Links order by hits desc limit 5

jerry

Or sticking with DBSQL:

my $db = new Links: BSQL "$LINKS{admin_root_path}/defs/Links.def";
my $top5recs = $db->query ( ID => '*', sb => 'Hits', so => 'DESC', mh => 5 );
if ($db->hits) {
foreach my $rec ( @$top5recs ) {
...
}
}

Hope that helps,

Alex

With this code I get the Top5 of all my database.

What I want is get the Top5 of each main category just, and not of all categories of the database.

Any thought?

Thanks

Ok, let's separate this into two component
situations:

1) finding the top 5 links in any category,
and listing those below the category on either the main page (home.html only) or on all the sub-category links (home.html and category.html)

2) finding the top 5 (or first 5) sub categories in each category and listing those under the category/subcategory link.

Option 1 allows you to promote the top links, option 2 allows you to give people an idea of what lays below each of the upper level categories.

A fancier option for #2 would have a field in the category defition that marks it as 'displayed' so it would be displayed under it's immediate upper level category. That gives the webmaster control of which categories get displayed. If the 'display'ed link was actually selected, you could also display the "new/updated" banner next to each of the sub-sections.

There is an Option #3 which is really only getting the cool-links to display on the home page in a list as "Top N Links"

Sorry guys, I dont if it´s because English is my sencond language, but I think you haven´t understood what I mean, ot if so, didn´t came with an answer, or even I am not understading you

For example. I have the followind categories:
Computer
Computer/MAC
Sports
Sports/NBA
Sports/Soccer
Travel
Travel/Europe

Then, what I want is to get the top x in Computer, another Top x in Sports and another in Travel, so I would have three listings of topx. One for each main category, and not having a top5 for *all* categories .
Is that a litle bit clear now?

Widgetz.
Can you show me what is the code you are using?

Thanks again...

Ok,

Do you want the Top 5 LINKS or the Top 5 categories under each main category?

Also, how do you want to define "top" -- the most Hits?

How is this possible in Links without SQL?

Lothar

i see an eliot wannabee! ahh!!

i already made both for Links 2..

they are hidden in the forum somewhere

jerry

Hi Jerry, Hi Pugdog,

I've searched the forum and find only this thread realy related to my search.

If you like, take a look at my site:
link:http://Download-Tip.de/
What I am looking for is to list the top 5 links/downloads of each main category on the main category site.

By Example, on

link:http://Download-Tip.de/Anwendungen/

I will list the top 5 of all subcategories of Anwendungen.

Lothar

Jerry,

Could you write to the forum a complete text about what to change for this mod ? or maybe you could put it in the resource center.

Thanks

Hello,

Can someone please give me more exact instructions on how to add this code.

How do I display the code in my templates? etc.

Thanks.

------------------
James L. Murray
PaintballCity.com
The Yahoo of Paintball
www.paintballcity.com
AIM: Paintball City