CODEWITHSUNDEEP
mysqldatabase-design
user1260310
user1260310

Reputation: 2227

Mysql/php order by most popular table structure

Is there a tried and true best practice for displaying most viewed posts? Do people normally set up a viewed table and count records? I believe this would require a join of the posts and viewed table when retrieving data. Is there a better way? Thanks.

Upvotes: 1

Views: 280

Answers (2)

Steve Robbins
Steve Robbins

Reputation: 13812

Perhaps create a views table with a unique id, post id, and IP address/unique identifier. That way you can sort by all views/unique views.

You order by would be 

SELECT [...], COUNT(DISTINCT(views.ip)) count
FROM posts
INNER JOIN views ON views.postid = post.id
ORDER BY count DESC
GROUP BY post.id

Upvotes: 1

Lefsler
Lefsler

Reputation: 1768

You can create a field called (visited) and touch it everytime someone open the page (set visited=visited+1) So you can order by visited.

I hope it helps

Upvotes: 1

Related Questions