How can I implement a basic "Most Popular Post" script in PHP?

Question

There seem to be very few tutorials out there about how to write a PHP script to show the "Most Viewed" posts. I've found some scripts custom made for WordPress and haven't been able to get any of them to work for me. The rest are most popular php scripts for programmers.

I have table called news with the fields id, count, timestamp, headline, img, text. I want to get the 5 most popular posts for the last week showing just number and headline.

How can I go about this?

Answer 1

This will grab 5 records from within the last week with the highest count value

$sql = "
SELECT * 
FROM newsTable 
WHERE timestamp > DATE_SUB(curdate(), INTERVAL 1 WEEK) 
ORDER BY count DESC 
LIMIT 5
";

EDIT

An easy way to increase the count field by 1 and update the timestamp

$sql = "
UPDATE yourtable 
SET count = count + 1, 
timestamp = NOW()
WHERE id = " . $post_id

Answer 2

The first thing you need to do is to add a count (or hits) column:

ALTER TABLE news ADD count INTEGER

Then whenever you display the news, increment the appropriate count:

UPDATE news SET count = (count+1) WHERE id = X

Then just show the most popular news:

SELECT * FROM news WHERE timestamp > DATE_SUB(curdate(),INTERVAL 1 WEEK) ORDER BY count DESC LIMIT 5