Weekly ranking and monthly ranking Development Summary (original)

Prerequisites: 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 roughly structured id contentid hits date1 2 12 2010-12-182 2 23 2010-12-193 1 15 2010-12-194 2 21 1. The first statistical step is to record the article click rate per day, 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, it is the first time you read this article on the day, you need to insert a record. When you read this article, you only need to update the click rate. This is to record the click rate of an article in 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 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, only click rate + 1} else {mysql_query ("insert into ranking ('tentid', 'hits ', 'date') values ($ contentid, 1, $ date) "); // if it is the first time you browse, insert a piece of data, click rate 1} 2. the query has been completed. Next, we need to query these articles in the order of the total click rate of one week or one month. This is a difficult issue. 1. group the article and calculate the total CTR: select *, sum (hits) from ranking group by contentid order by sum (hits) desc2. filter the 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 query statement for weekly ranking, which is relatively complicated, after the query is made, it is displayed in the array. The monthly ranking is also like this. Just change the function and I will not write the complete PHP code. Http://bbs.2cto.com/mode.php? M = o & q = user & u = 53700.