Obtaining an average per day value of a field with MySQL's DATEDIFF function
filed under: Web Development / MySQL
Quite some time ago, I added a views field to my articles table, and implemented a small function to log a "view" on each of my articles as it was read. A lot of CMS & blog systems do this, and people often display the number of clicks/views/reads on the article page.
While I don't display that specific information currently on my articles, I wanted to calculate the average number of views/reads per day as a measure of determining which of my articles were the most popular, as simply putting up the total number of view/reads is basically going to give you a persistent list of the oldest articles, unless you get dugg, that list will likely not change.
This is easy to accomplish when using a MySQL datetime field for the article publish date.
Here is the basic MySQL code used;
SELECT title, views / DATEDIFF(CURDATE(), date) AS views_per_day
FROM articles
WHERE DATEDIFF(CURDATE(), date) > '14'
AND views > '100'
ORDER BY `views_per_day` DESC
LIMIT 0, 10
This produces a list of the top 10 articles (over 14 days old with an excess of 100 views to avoid recent/front page posts diluting results), and displays their titles along with the average views per day, descending in order from highest views per day.
Comments
No comments posted yet, why not be the first?
Categories
Recent Entries
Recent Comments
- Dede (I checked it today in a shop. GT2 had some troubles with six ...)
- Matt (Bintang, You need to re-direct the url, try ...)
- jesth (Ohh.. why didn't I think of that, thanks alot.)
- Matt (Dede, I don't have Gran Turismo 2, any of the 2nd+ generation ...)
- Matt (Jesth, Just change the if condition, instead of looking for ...)
- jesth (Hi (again) Was wondering, is it possible to make it ...)
- Bintang Sembilan (Matt, thanks for your modd. I have apply it to my ...)
- Dede (Hello there. Can you check something for me? I want to buy ...)
- Matt (I think it's a driver issue Terrence, or it was a driver issue. ...)
Popular Entries
- Light-weight related articles mod for sNews 1.7 (4.5/5)
- Image / math hybrid captcha version 2, vastly improved (4.42/5)
- 1024x600 netbook wallpapers of Evangeline Lilly (4.4/5)
- Compact archives for sNews 1.7 (4.4/5)
- sNews Ajax Polls mod now available (4.38/5)
- Pretty date and comments bars in sNews CMS (4.35/5)
- Page caching mod for sNews 1.7 (4.33/5)
- Gravatar mod for sNews 1.7 (4.29/5)
- An improved tag cloud for sNews 1.7 (4.29/5)