Hi,

Am adding extra features to a blog website — they need help counting how many times a post/article is being viewed to generate other info like most read post,most Favorited posts etc..

How do i design the db table to accommodate this and how do i construct and implement the above features from the code end..

pageview counter

Everytime there is a page requested you increment the counter.

Create a table in Database that has an article ID and a count column with 0 as a default value. every time the page is being viewed add 1 to that count column.

Hello,

Add one integer column (Visited) to the ARTICLES table with Default Value 0 (zero).

On the Article Show page, add the following code.

    var id = UrlData[0];
    var data = Database.Open("db");
    var sqlQ = "SELECT ARTICLEID, Title, InfoShort, ... , Visited FROM ARTICLES WHERE ARTICLEID = @0";
    var info = data.QuerySingle(sqlQ,id);  

    int incVisited = info.Visited; 
    incVisited++;
    var v1 = "UPDATE ARTICLES SET Visited=@0 WHERE ARTICLEID= @1";
    data.Execute(v1, incVisited, id);

Mark as answer if it helped

saeed_saedvand

Create a table in Database that has an article ID and a count column with 0 as a default value. every time the page is being viewed add 1 to that count column.

This method is easy to code up and employ however this will only work if you want total hits – Not unique hits, further user validation would be required to capture only unique (per user traffic)..

A-rad,

Thanks for the insight, but how do I only count from unique hits and not just total hits

hardweezy

A-rad,

Thanks for the insight, but how do I only count from unique hits and not just total hits

This might be complicated for a beginner. You would need to build something that relates to the session of the user (their unique IP inclusive). There are people that have tried this (http://stackoverflow.com/questions/7005904/how-to-capture-unique-user-sessions-in-webmatrix-razor-asp-net-web-pages)
but there also might be an out-of-box open source solution to this too.

I also found this code where someone built a desktop application to monitor the traffic on their website (razor site), components of this may assist you in your enquiry: http://www.codeproject.com/Tips/479303/Easy-Desktop-Web-Stats

In the complexity of learning this algorithm, I think I have found the closest to identifying a unique visitor using Session-State..

Can I have this interpreted into Razor Syntax

string vidID = Request.QueryString["v"];

        SqlConnection conn;
        SqlCommand cmd;

        if (!Page.IsPostBack)
        {
			if(!string.IsNullOrEmpty(vidID)) //kicjk it if the quesystring is not passed
				if(Session[vidID] == null) //Session is null means user is visiting for first time
				{
					conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["XXX"].ConnectionString);
					cmd = new SqlCommand("UPDATE TabVideos SET VideoHits = VideoHits+1 WHERE VideoID=@VideoID", conn);
					cmd.CommandType = CommandType.Text;

					cmd.Parameters.AddWithValue("@VideoID", vidID);

					using (conn)
					{
						conn.Open();
						cmd.ExecuteNonQuery();
					}
					
					Session[vidID]  = vidID;
				}
			}
        }

Add Google Analytics to the site and then query it using their Reporting API.