Weekly ranking and monthly ranking development summary (original)_PHP tutorial

Preliminary knowledge: group by, MYSQL functions week(), month()

When designing a database, there is usually a field to record the click-through rate of an article. If we want to calculate the click-through rate ranking for a week or a month, it is definitely impossible to achieve it by relying on this field alone. At this time, a new table will be created to record the daily click-through rate of each article.

Assume that this table is named ranking, and four fields are defined: rid (table ID), contentid (associated with article ID), hits (records daily click-through rate), date (time, important, compared during query)

Rough structure of ranking

id contentid hits dates

1 2 12 2010-12-18

2 2 2 23 2010-12-19

3 1 15 2010-12-19

4 2 21 2010-12-20

1. Statistics

The first step is to record the daily click-through rate of the article. This step is very simple. When the user views an article, the PHP program will perform a database query to determine whether the record exists. If it does not exist, it means that it is that day. When browsing the article for the first time, a record needs to be inserted. When subsequent visitors read the article again, they only need to update the click-through rate. This is to record the click-through rate of a certain article for a day.

PHP:

$date = date("Y-m-d",time());

$contentid = $_GET[id];//Current article ID

$query = mysql_query("select * from ranking where contentid=$contentid and date=$date); //Query 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 rate +1

}else{

mysql_query("insert into ranking (`contentid`,`hits`,`date`) values($contentid,1,$date)");//If it is the first time browsing, insert a piece of data, click rate is 1

}

2. Inquiry

At this point, the statistical work has been completed. Next, it is difficult to query these articles in order of the total click rate for a week or a month.

1. First group the articles and calculate the total click rate: select *,sum(hits) from ranking group by contentid order by sum(hits) desc

2. Take this week’s data and filter it out: select *,sum(hits) from ranking where week(date)=week(now()) group by contentid order by sum(hits) desc

This is the query statement for weekly ranking. It is relatively complicated. After querying, it is put into an array and displayed sequentially. The same is true for monthly ranking. Just change the function. I will not write the complete PHP code.

http://bbs.2cto.com/mode.php?m=o&q=user&u=53700

http://www.bkjia.com/PHPjc/486038.htmlwww.bkjia.comtruehttp: //www.bkjia.com/PHPjc/486038.htmlTechArticlePreliminary knowledge: group by, MYSQL functions week(), month() generally have a field when designing a database To record the click-through rate of the article, if we want to count the click-through rate ranking for a week or a month...