CODEWITHSUNDEEP
phpmysql
Adam Ashwal
Adam Ashwal

Reputation: 1472

How to create a 'rising' or 'What's hot' query in Mysql and PHP?

I'm trying to create a filter to show certain records that are considered 'trending'. So the idea is to select records that are voted heavily upon but not list them in descending order from most voted to least voted. This is so a user can browse and have a chance to see all links, not just the ones that are at the top. What do you recommend would be the best way to do this? I'm lost as to how I would create a random assortment of trending links, but not have them repeat as a user goes from page to page. Any suggestions? Let me know if any of this is unclear, thanks!

Upvotes: 4

Views: 1540

Answers (3)

sisve
sisve

Reputation: 19781

You're mentioning that you don't want to repeat items over several pages which means that you can't get random ordering per request. You'll instead need to retrieve the items, order them, and persist them in either a server-wide or session-specific cache.

A server-wide cache would need to be updated every once in a while, a time interval you'll need to define. Users switching page when this update occurs will see their items scrambled.

A session-specific cache would maintain the items as long as the user browses your website, which means that the items would be outdated if your users never leave. Once again, you'll need to determine a time interval to enforce updates.

I'm thinking that you need a versioned list. You could do the server-wide cache solution, and give it an version (date, integer, anything). You need pass this version around when browsing the latest trends, and the user will keep viewing the same list. Clicking on the Trends menu link will send them to the browsing pages without version information, which should grab the latest from your cache. You then keep this as long as the user is browsing these pages.

I can't get into sql statements, not because they are hard, but we don't know your database structure. Do you keep track of individual votes in a separate table? Are they just aggregated into a single column?

Upvotes: 1

Venice
Venice

Reputation: 1848

This response assumes you are tracking up votes in a child table on a per row basis for each vote, rather than just +1'ing a counter on the specific item.

Determine the time frame you care about the trending topics. Maybe 1 hour would be good?

Then run a query to determine which item has the highest number of votes in the last hour. Order by this count and you will have a continually updating most upvoted list of items. 

SELECT items.name, item_votes.item_count FROM items
INNER JOIN
(
    SELECT item_id, COUNT(item_id) AS item_count
    FROM item_votes
    WHERE date >= dateAdd(hh, -1, getDate()) AND 
          ## only count upvotes, not downvotes
          item_vote > 0
    group by item_id
) AS item_votes ON item_votes.item_id = items.item_id

ORDER BY item_votes.item_count DESC

Upvotes: 3

Ethan H
Ethan H

Reputation: 625

Maybe create a new column to record how many views it has? Then update it every time someone views it and order by threads with the largest number of views.

Upvotes: 0

Related Questions