Weekly ranking, monthly ranking Development Summary (original) _ PHP Tutorial

Weekly ranking, monthly ranking Development Summary (original ). Prerequisites: groupby, MYSQL functions week () and month () generally have a field to record the article's CTR when designing the database, if we want to count the rankings of click rates for one week or one month: group by, MYSQL function week (), month ()

When designing a database, there is usually a field to record the article's Ctr. if we want to calculate the weekly or monthly CTR rankings, this field is definitely not feasible. Create a new table to record the daily click rate of each article.

Assume that this table is named ranking and defines four fields: rid (table ID), contentid (associated with the article ID), hits (record the daily click rate), date (time, important, comparison during query)

Ranking 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

I. Statistics

The first step is to record the article's daily click rate. This step is very simple. when you view an article, the PHP program will perform a database query to determine whether the record exists. if it does not exist, this is the first time you browse this article on the current day. you only need to update the click rate when you insert a record. This is to record the click rate of an article in a day.

PHP:

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

$ Contentid = $ _ GET [id]; // The ID of the current article.

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

} Else {

Mysql_query ("insert into ranking ('contentid', 'hits ', 'date') values ($ contentid, 1, $ date)"); // if it is the first time you browse, insert a data entry with a click rate of 1

}

II. query

Now that the statistics have been completed, it is difficult to query these articles in the order of the total click rate of one week or one month.

1. group the article and calculate the total CTR: select *, sum (hits) from ranking group by contentid order by sum (hits) desc

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

This is a weekly ranking query statement, which is relatively complex. after the query is made, it is displayed in the array in sequence. The Monthly ranking is also like this. just change the function, I will not write the complete PHP code.

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

Sort by, MYSQL functions week (), month () usually have a field to record the article's CTR when designing the database. if we want to count the ranking of CTR for one week or one month...