Voting/Comparison logic.

[gorgon2k]

I'm working on a project that will use a comparison voting logic to sort the highest rated to the top and lowest to the bottom(Similar to a "hot or not" or "Hotstagram"). Basically what I need to do is take 2 random pictures that are directly next to each other in the database and have users vote, adding one point to the winner and subtracting on point from the loser essentially filtering the highest to the top.

 

My question is two fold, How can I get a random item in a MySql database then get a random item directly next to it. and secondly (if anyone has prior experience with this) how would you structure your DB? I'm thinking One table for images and a second that will hold the votes(then compile the results on page load). I guess what my concern is here, for the ranking, ever new entered photo will start at zero, so you could have X amount of photos with the same rank? I'm just throwing some thoughts out there and I need another mind to toss this around.

 

There's probably a term for this type of ranking, something similar to bubble sorting but I'm not quite sure what you could call it.

 

Also, just thought of this: If you have say the 45th ranked photo vs the 44th ranked photo would you +1 the winner and -1 the loser or just +1 the winner? I'd image that this sort of logic needs to have a negative effect on the loser of the vote to truly filter the results.

[ignace]

I'm thinking One table for images and a second that will hold the votes

 

Yes. But to make the querying easier you can add a votes colum to the images table and increment it everytime it receives a vote. The voting table then keeps a record of which user's voted for the image.

 

To select two random images from your table I would suggest you read: http://jan.kneschke.de/projects/mysql/order-by-rand/

[gorgon2k]

I'm thinking One table for images and a second that will hold the votes

 

Yes. But to make the querying easier you can add a votes colum to the images table and increment it everytime it receives a vote. The voting table then keeps a record of which user's voted for the image.

 

To select two random images from your table I would suggest you read: http://jan.kneschke.de/projects/mysql/order-by-rand/

 

So you say store votes in one table but increment the photo table to prevent the need to join for some queries? Also, do you think it should be +1 -1 or just +1?

[ignace]

So you say store votes in one table but increment the photo table to prevent the need to join for some queries?

 

Yes.

 

Also, do you think it should be +1 -1 or just +1?

 

I have no idea what you mean by this.

[gorgon2k]

So you say store votes in one table but increment the photo table to prevent the need to join for some queries?

 

Yes.

 

Also, do you think it should be +1 -1 or just +1?

 

I have no idea what you mean by this.

 

Sorry I should have clarified. When presented with the front end you have the option to vote on A or B. When you select say B, and you add one to B's rank, would you remove 1 from A's rank? or leave it at 0? I'd image that for this type of system you'd have to have one up one down for every vote.

[ignace]

Sorry I should have clarified. When presented with the front end you have the option to vote on A or B. When you select say B, and you add one to B's rank, would you remove 1 from A's rank?

 

Increase only.

 

Don't forget to add a date_voted column to the voting table or otherwise you'll have newly added photos competing against photos with a million votes. You should have different kind of rankings (this week, this month, this year, all-time). Maybe categories too if you are going to get a lot of photos.