How to create a rating system using MySQL and PHP, like 9gag?

[FarrisFahad]

I have a database of pictures, and votes. The pictures database has up_votes and down_votes culomns. I want to create a system using MySQL to get hot and trending pictures just like 9Gag.
How can I do that?

[rproffitt]

I think the how involves mostly funding. Why do I think this? https://www.google.com/search?q=How+to+create+a+9gag+website

So the problem is mostly funding. It can't be a coding issue.

[FarrisFahad]

What does funding have to do with voting system? I am asking about the way to code a voting system like 9Gag with MySQL and PHP.

[FarrisFahad]

Here is something I am looking for ...

mysql_query("SELECT * FROM pictures WHERE (up_votes - down_votes) * 0.6 > average_votes ORDER BY date DESC LIMIT 15");

I am looking for a query that sort hot and trending pictures. I was thinking that "hot" could be amongst the top 60% and trending above 30%.

What do you think? Is there a better way.

[rproffitt]

Check out the link I gave and a few articles. The scripts for 9gag like sites is out there. The money has everything to do with getting a site to that scale and reach.

I see you added some example SQL query but you asked "just like 9Gag." To which you can get one of those scripts and then focus on promotion, hosting the millions of posts and the team you need to moderate and monetize.

[FarrisFahad]

You still don't understand the question. I already have a website similar to 9Gag, but I want to replicate their voting system. I don't know the algorythm. I don't want to build a clone site like 9Gag. But thank you for your reply.

[alan.davies]

Your hot or trending images should have a time eleme t to it too, as in 'only the last 3 days' for example. Otherwise the same old image could be top of the pops forever.

[FarrisFahad]

This is where it's tricky. I want to display best images for today and all days. I just don't know the SQL algorythm. This is why I am asking.

[pty]

It's not just the algorithm, as @alan.davies said, the data plays a part. Instead of storing up or down votes as integers, you probably need to introduce a new relation (let's refer to it as Vote) that has a post_id and a user_id which are foreign keys to the posts and users tables respectively.

Now, a user can only vote once per post so add a unique index that covers post_id and user_id. And obviously you need a direction (up or down, perhaps you could store this as a +1 or -1) and timestamps (created_at, updated_at).

Now, it's trivial to work out not only how people voted but when they voted. You have enough data to implement the Hacker News or Reddit algorithms.

[alan.davies]

I'd just add to pty's post that you may benefit from keeping some non-normalised fields that gets updated everyday - this can be done with a cron job, which keeps today's tops and all time tops. This all depends on the volume of votes you get though. I'll flesh out the idea if we go down that path.

[FarrisFahad]

Okay here are some solutions I have come up with to give you an idea of what I want ...

SELECT * FROM pictures WHERE (votes * 0.60) >= (SELECT AVG(votes) FROM pictures) ORDER BY date DESC LIMIT 15

Let me explain the code above: I am selecting the top 60% pictures according to average votes for all pictures, and ordering them according to timestamp.

But the problem with the SQL above is that, like @alan.davies said, this will sort according to votes not date.

I want to come up with a way to sort today's funny pictures first, then yesterday, then the day before, etc ...

SELECT * FROM pictures WHERE (votes_up - votes_down) >= (SELECT AVG(votes_up - votes_down) FROM pictures WHERE uploaded_timestamp >= time() - (60*60*24*7)) ORDER BY uploaded_timestamp DESC LIMIT 15 

The code above will get you pictures that have a score above or equal to average score for pictures in the last 7 days ordering them by date

I think the main question here is how do I sort according to date first, then votes or score.

The problem with date is that it's a timestamp which means it's changes every second. This will cause the sorting to be according to date and not date + votes.

Do you have any idea how to solve this?

[pty]

If you're not even going to read the links I post there's no point in responding. The algorithms are even listed and annotated. You could convert that code to SQL quite easily, or do the heavy lifting in SQL and the ranking in PHP.

Nobody's going to spoonfeed you code.

[FarrisFahad]

Thank you the second article helped, Sorry I guess I was looking for something simpler than link 1.

[pty]

No problem. The Hacker News one may be a little advanced but it was written by Paul Graham so it's to be expected. The Reddit one is definitely more digestable and shouldn't take more than a few minutes to implement and tweak to your tastes.