Viewing Most Popular Links - Revisited > Mitchel's Personal Web Portal

WebSite Search

Mitchel Sellers' DotNetNuke, .NET, and Other Topics Blog

Subscribe To Blog Updates by E-Mail

Current Articles | Categories | Search | Syndication

Viewing Most Popular Links - Revisited
By IowaComputerGurus Host on Thursday, July 19, 2007 @ 5:02 AM

193 Views :: 0 Comments ::

:: DotNetNuke, SQL

A little over a month ago I posted a SQL Query that can be used to find the most popular links on a given portal. In that example if you had links that were being tracked that were files it would only show the fileid for the file and not the file name. Now since there has been a fair amount of reader demand I have created a new version of this query to actually show the URL for URL based links and to show the file name for file based links.

To successfully parse the "fileid=xx" portion of the results when needed I use the substring function to grab just the id so I can join to the files table to grab the file name. Due to the different methods needed for files and links I have done this via two queries and then used the "UNION" statement to combine the results for display. For you to use this on your portal you will need to be sure to modify the portalid value to match that of your portal.

--Get standard links
SELECT m.ModuleTitle,
        t.url,
        t.clicks,
        t.LastClick
FROM urltracking t
    INNER JOIN Modules m
        ON (t.ModuleId = m.ModuleId)
WHERE t.PortalId = 0
    AND t.trackclicks = 1
    AND t.urltype <> 'F'
--Combine with the others
UNION
--Get File links
SELECT m.ModuleTitle,
        f.[FileName] as url,
        t.clicks,
        t.lastclick
FROM urltracking t
    INNER JOIN Modules m
        ON (t.moduleid = m.moduleid)
    INNER JOIN Files f
        --Grab the file id from the url field for the join
        ON (CAST(SUBSTRING(t.url, 8, LEN(t.url)) as int) = f.fileid)
WHERE t.urltype = 'F'
ORDER BY t.clicks DESC, url

Please post any comments/questions below


	Share/Save This Article
	Use the below controls to save this article to one of many popular social bookmarking sites!


	Article Rating
	12345


	Article Comments
	Currently, there are no comments. Be the first to post one! Click here to post a comment