How To Retrieve Most Visited Posts Of A Day, Month And Year ?

Yii 1.1 General Discussion
1

Hello guys.

I am currently working on a project in which I want to display the most visited posts of a day, month and year on sidebar. just like in the attached picture

I have already set the views option in my post i.e. when a post is being viewed then the view field value is increasing.

But now I am confusing that which type of query should I wrote to retrieve the most visited posts of day, month and year. Also I want to set the criteria for retrieving the record ?

thanks in advance

2

SELECT * FROM tbl_post ORDER view DESC limit 1

3

Dear I want to retrieve the data on DAY, MONTH and YEAR basis.

So the query should follow these conditions.

4

As far as I can see, you don’t store the view count with sufficient granularity to do what you want to do. You can’t distinguish when the view occurred, so you have no way of knowing how many of the views happened within the last 24 hours (or any other period).

5

thanks dear for your response.

I am storing the views value in the database using




public function actionView()

	{

		$post=Post::model()->findByPk($_GET['id']);

                $post->updateCounters(

                    array('views'=>1)                        

                );

		$comment=$this->newComment($post);


		$this->render('_detailview',array(

			'model'=>$post,

			'comment'=>$comment,

		));

	}

but if I am going wrong then plz mention it.

6

It doesn’t provide enough information for you to query by period.

The simplest approach would be to create a separate table (say post_views) with a single entry for each view, recording the post ID and the view timestamp. That could get very large very quickly though.

To minimise the overhead, you could possibly use a table tracking views per day. The table would need to record the post ID, the date (could just be the integer portion of a Unix timestamp) and the view count for that day. When a page was viewed, you would check to see if a view count record exists for the relevant post and current day. If not, you would create a new record, initialising the view count to 1. Otherwise, you would increment the view count.

It seems like a lot of work, but I can’t think of a simpler way of achieving what you’re trying to do.

7

thanks to all of you dear.

I have solved my problem using Getting the recent one month or year records from MySQL table

8

As indicated by Keith, AND your link, You need to have a date the post was viewed.