The Ashes

Technology, Science and other news
April 10, 2009

Building a Tag Cloud With SQL and ASP.NET

Posted by : admin
Filed under : General

Categories and tags are an extremely valuable addition to any website. 
They are an excellent opportunity to include relevant keywords on pages
within your site.  How else can you legally create that kind of keyword rich internal hyperlink
magic?  SEO benefits aside, your users gain simplified access to the
content that they want to see which promotes longer, deeper visits (pageviews!).

Building the Database Structure
We
already have an Articles table in place.  The only reference relevant
here will be the unique ID.  We will have to add a list of the tags and
or categories and a table to link the two.  I will list all the tags
into a table called Tags, and will create a table called ArticleTags to
link the two tables together.

Articles ArticleTags Tags
  • ID
  • Title
  • ID
  • TagID
  • ArticleID
  • ID
  • Tag

The Queries

  • Get Tags For an Article
    • SELECT t.Tag FROM Tags t
      INNER JOIN ArticleTags at ON t.ID = at.TagID
      WHERE at.ArticleID = @articleID

  • Get Articles By Tag
    • SELECT a.ID From Articles a
      INNER JOIN ArticleTags at ON at.ArticleID = a.ID
      WHERE at.TagID = @tagID

  • Get Top Tags (for tag cloud or a simple tag list)
    • SELECT
      TOP 30 t.Tag FROM Tags t
      INNER JOIN (SELECT COUNT(at.ID) as TagCount,
      at.ID FROM ArticleTags at GROUP BY at.ID) as tt
      ON tt.ID = t.ID ORDER
      BY tt.TagCount DESC

      • This creates a list of the top 30 tags
        used.  If you want to create a tag cloud from this result set you will
        have to append another select into this data set in order to put it in
        to alphabetical order.  Changing the sort from tt.TagCoung to t.Tag
        will not put the top 30 tags in to alphabetical order but return the
        first 30 tags.  You can also do this in your business layer using a
        DataTable's DefaultView.Sort = "Tag ASC";

You
can use this to get started displaying the top tags around your site. 
Simply replace tags with categories and you have a category system in
place as well.  The benefits are plentiful reason enough to implement
these ideas both for you and your users.  The next step is displaying
the Tag Cloud at the proper size which I will cover very shortly.

Tags :

No Comments

(required)
(will not be published) (required)
(opitional)

cciash.com EN ES IT DE PT CZ FR RU
October 2018
M T W T F S S
« Sep    
1234567
891011121314
15161718192021
22232425262728
293031  

Pages

Categories

Resources

There are many online poker site where you can play but at poker.hk you can play the poker games with all the knowledge you need related to the game with the poker school available in both the English and Chinese language.

Super Casino

Now you can bet on any sports and any sporting events from all the comfort from your home. Bet770 allows you to bet on any events and match with in just 3 clicks. They also offers great odds on football betting for every premier and champions league match. Get £50 free in bets when you register.

Bingo770, offering best online bingo games with £7.70 free no deposit Bonus!