using (SqlConnection con = new SqlConnection(connectionString))
{
string qry = "UPDATE MostVisited SET MostVisited = MostVisited + 1 WHERE NewsID=" + id;
using (SqlCommand cmd = new SqlCommand(qry, con))
{
con.Open();
cmd.ExecuteNonQuery();
}
}
Make sure that you inserted a row in the new table while you create a news in News table
siamand
Member
416 Points
307 Posts
How to do Most Viewed items .. or popular article ...
Mar 11, 2012 05:21 AM|LINK
Hi all,
if i have a table in sql database called News consisit of :
NewsID int pk
NewsTitle nvarchar (150)
Content nvarchr(max)
now what i wantt to do , is create a coloumn or a block to show thoses news which are most visited or viewed ..
what i need to do ?
i hope you understand what i mean
Regards
Bimalvv
Contributor
2372 Points
481 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 05:42 AM|LINK
I think it is better to have a different table in same database with following fields
VisitedID int PK
NewsID int FK
MostVisited int
As it will be easy to increment the MostVisited field once the users view a particular news
Bimal
siamand
Member
416 Points
307 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 05:44 AM|LINK
Thanks for your replay
i think its a good idea ..
so what will be next step ? how to the coding using C sharp and visual studio to increase the number in MostVisisted field?
Regards
Bimalvv
Contributor
2372 Points
481 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 05:48 AM|LINK
Retrive the MostVisited value with the help of NewsID, increment it and then update
Bimal
siamand
Member
416 Points
307 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 07:01 AM|LINK
actually im biggener and dont know how to do it .. could you explain it more in steps
much apreciated
Regards
Bimalvv
Contributor
2372 Points
481 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 07:55 AM|LINK
Create the table with above mentioned fields.
using (SqlConnection con = new SqlConnection(connectionString)) { string qry = "UPDATE MostVisited SET MostVisited = MostVisited + 1 WHERE NewsID=" + id; using (SqlCommand cmd = new SqlCommand(qry, con)) { con.Open(); cmd.ExecuteNonQuery(); } }Make sure that you inserted a row in the new table while you create a news in News table
Bimal
siamand
Member
416 Points
307 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 07:57 AM|LINK
Thanks for your help,
i will try your code , and hope that i can make it work ...
Regards
siamand
Member
416 Points
307 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 08:31 AM|LINK
one thing , what you mean by : make sure you inserted a row in the new table ... ?
Bimalvv
Contributor
2372 Points
481 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 04:15 PM|LINK
When you add a new News item, you need to insert one row in the MostVisited table also with NewsID and MostViwed(which will be 0 for first time)
Bimal
Dan Bracuk
Contributor
3970 Points
1096 Posts
Re: How to do Most Viewed items .. or popular article ...
Mar 11, 2012 04:59 PM|LINK
I'd use a different approach. I'd go with
Table Name NewsVisit
NewsId int
DateTimeVisited date
Then to get your most popular article, do something like this
select NewsTitle
from
(select NewsTitle, count(NewsTitle) theCount
from News n join NewsVisit nv on n.NewsId = nv.NewsId
where some date range thing
group by NewsTitle ) allCounts
join
(select top 1 visitCount maxCount
from
(select newsId, count(NewsId) visitCount
from NewsVisit
where the same date range thing
group by newsId
order by visitCount desc ) visitCounts
) topCount on maxCount = theCount