Article click Weekly ranking, monthly leaderboard function Development Summary

Article click Weekly ranking, monthly leaderboard function Development Summary, Preliminary knowledge: Group By,mysql function Week (), month (), below we take an example to analyze how to develop a weekly ranking of articles, monthly ranking function.

When designing a database, there is generally a field to record the click-through rate of the article, if we want to count a week or one month Ctr ranked by this field is definitely not possible. A new table will be created to record the clickthrough rate of each article per day. Assuming that the table is named ranking, define four fields: RID (table ID), ContentID (associated with the article ID), hits (record daily Ctr), date (time, important, query comparison)

Ranking approximate structure

ID ContentID hits Date

1 2 12 2010-12-18
2 2 23 2010-12-19
3 1 15 2010-12-19

4 2 21 2010-12-20

First, statistics

The first step is to record the daily click-through rate of the article, this step is very simple, when the user to view an article, the PHP program will be a database query, determine if there is a record, if not exist, the first time to browse the article that day, you need to insert a record, the following visitors read this article, Just update the CTR on the line. This is the click-through rate that records the day of an article.

$date = Date ("Y-m-d", Time ());
$contentid = $_get[id];//Current article ID
$query = mysql_query ("SELECT * from ranking where contentid= ' $contentid ' and date= ' $date '); Querying the database
if ($value = mysql_fetch_array ($query)) {
    mysql_query ("update ranking set hits = hits+1 where id= ' $value [id] ');//If there is a record, just click +1
}else{
    mysql_query ("INSERT INTO ranking (' ContentID ', ' hits ', ' Date ') VALUES (' $contentid ', ' 1 ', ' $date ')");//If it's the first time you're browsing, insert a piece of data, CTR is 1
}

Second, query

At this time the statistical work has been completed, the next to the article according to a week or one months the sum of the order of the query out, this is a difficult point.

1. First to group the article and calculate the total Ctr:

Select *,sum (hits) from ranking group by ContentID ORDER by sum (hits) desc

2. Take this week's data to filter out:

Select *,sum (hits) from ranking where week (date) =week (now ()) group by ContentID ORDER by sum (hits) desc

This is the week row of query statements, relatively complex, query out and then put into the array in turn to display, the monthly ranking is the case, change the function on the line, the full PHP code I will not write it out.

