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.