Today's most popular

[eluser]gullah[/eluser]
I'm looking to have a feature on my site with the functionality of Today's most popular X.

Maybe I'm just being uncreative but the only way I can see doing this is to have a db field and increment it on views or actions and then have a cron/stored procedure reset that field at midnight or some other arbitrary time.

Is the database going to be able to quickly handle a large stored procedure that resets everything to zero without interrupting operations? Is there a better way?

[eluser]Teks[/eluser]
I think, that this depends on HOW you are going to calculate which X is most popular.

If visitors are going to be voting, for instance, then all you have to do is pick the item from the database with most votes.

If you want things to happen more automagically, you could have a 'visit_count' field in your database, and every time someone visits a page with an X thing, you have your controller add 1 to that field in the database. Then, again, all you have to do is query the database for the X with the highest 'visit_count'.

I hope this helps.

[eluser]gullah[/eluser]
yea the 'magic' behind the popular is still be decided. The problem is how to get it to reset every night. I decided to go with a cronjob at midnight which does some magic and essentially resets it.

[eluser]Teks[/eluser]
If your database records have an 'updated_at' stamp - that is, a datetime stamp of the last time they were updated - then, again, you can do this without a cron job, and with a simple database query: "give me the X with the highest 'visit_count', and whose 'updated_at' is today".

[eluser]gullah[/eluser]
I think you are misunderstanding what I'm trying to do.

I want to views for that day only, not including the rest. so A could be popular on day 1 and the next day A gets no views but B is really popular. I want B to be at the top and A on the bottom. Therefore the need to have an equal starting point to compare.

[eluser]Teks[/eluser]
I think I understood correctly - but maybe I did not explain myself too well.

Let's say that you have a site, where you sell SPROCKETS. You store information on each different type of sprocket, in a database, in a table called 'sprockets'. That table has fields to store sprocket size, colour, model number, and everything else you need.

Now, you want to find out which are the MOST OFTEN VISITED sprocket pages. Not only that: you wish to find out which ones are the most visited, on a *daily* basis - that is: which sprocket was most often visited TODAY. This means that today, sprocket A might be the most popular, but tomorrow, sprocket Z may be the favourite.

There are, of course, many different solutions for this. The one that first came to my mind was as follows:

1) in your 'sprockets' table, add a DATE field called "last_visited"
2) then, add an UNSIGNED INTEGER field called "visit_count"

Now, if you are using CodeIgniter, you would already have a 'sprocket controller' that is receiving the requests to display a 'sprocket page'. This controller, most likely, gets the 'sprocket model' to retrieve the information from the database, then sends the information to the 'sprocket view'. Right?

3) in your 'sprocket model', modify the code that retrieves the sprocket information from the database, so that on every sprocket retrieval it also does the following, using your new database fields:
- if 'last_visited' is [TODAY], then add 1 to 'visit_count'
- if 'last_visited' is NOT [TODAY], then put 1 into 'visit_count' and put [TODAY] into 'last_visited'

That's all you have to do.

Now, you have to think a little about the information you are storing. Each 'sprocket' now knows, WHEN (= what date) it was last visited, and HOW MANY VISITS it had on that day. That makes it extremely easy for you to find out what sprocket is the most visited today - this is a simple, single SQL query, which will be something like this:

SELECT * FROM sprockets WHERE last_visited=[TODAY] ORDER BY visit_count DESC LIMIT 1

"[TODAY]" is the DATE value for TODAY, which your model/view/controller scripts should automatically insert when making the query.

There is no need for a cron job, or any extra programming. Every time your view or controller queries the database, it will give you the most visited sprocket, at that precise moment, for that day.

I hope this helps.