Hello guys and gals from DP, I need some help. I know a little PHP, but not SQL. I would like to run this query I'm trying to display the most thanked posts from a board, using the reputation system. Let's suppose, we have a dislike and a like button. When somebody presses like, it gives him +X rep, and when somebody dislikes, it gives him -X rep. In order to display the tops posts I need to know the 10 TOP Postids with most thanks and less dislikes. This could be done by 2 ways: 1. In PID column, count all of the pids that have value in reputation column positive, later do the same with negative, rest negative out of positive +X-X and the 10 posts that get the biggest value from the operation are top posts. 2. Find most repeated PID with Positive Reputation and less repeated with - reputation (Top 10) The second way would be much more fastest than the first, but which one is better? And how do you do it with SQL :S
Yeah, sure Table vbulletin_reputation (It's not vbulleting board thought) Columns: rid (Reputation ID), UID (User id the reputation has been given to), ADDUID (User that has added the reputation), PID (Post the reputation has been given to), Reputation (Value, this is where the action centers), dateline, comments. That's all Thanks in advance for looking at my problem
I would probably use 2 queries to do this. 1 for the positive and another for the negative. It's probably possible to get it into a single query, but it would take quite a bit of logic on the database side. Something like this should work. SELECT PID, SUM(Reputation) AS reputation_sum FROM vbulletin_reputation ORDER BY reputation_sum DESC GROUP BY PID LIMIT 10 For the negative, just do the opposite. SELECT PID, SUM(Reputation) AS reputation_sum FROM vbulletin_reputation ORDER BY reputation_sum ASC GROUP BY PID LIMIT 10
Yeah well, but by those queries you would get TOP 10 Positive and TOP 10 Negative But Imagine for example, if the TOP 1 Positive would have 3 positive numbers and Top 2 2 positive It would be like TOP 1 - 3 Points TOP 2 - 2 Points But, what if TOP1 would have also 33 negative points and TOP 2 wouldn't have any negatives? It would still stay the same, even thought the top 1 would have -30 and not +3 points.
Since it's summing, it would add a +1 for a positive and a -1 for a negative. So, the first query would give the top 10 combined, and the second would be the bottom 10 combined. If a user had +2 and -30, the SUM would equal -28. If it were counting using COUNT() and not SUM(), it would be the most votes, but since it is summing, the result should be the aggregate sum for the user.
In theory it's awesome, however in practise it gives: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY pid LIMIT 10' at line 4 However, I have changed the query to SELECT pid, MAX( reputation ) FROM vbulletin_reputation GROUP BY pid LIMIT 10 But now it gives me at first the -1 PID, aka no PID speciefied when giving a reputation. Is there any way to exclude PID -1 from the list?
My bad, switch the GROUP BY and ORDER BY. SELECT PID, SUM(Reputation) AS reputation_sum FROM vbulletin_reputation GROUP BY PID ORDER BY reputation_sum DESC LIMIT 10
Alright, the last question: By setting up PHP and HTML, and running the query I get something like 15300 4 Points 15302 4 Points 15600 4 Points 15285 4 Points 15295 4 Points 15297 4 Points 15552 -4 Points The numbers on the left are the Post ID's, and on the right, the thanks recieved, of course. Now, I need to replace the PIDs with Subjects of the post To do this, I have a database called vbulletin_posts, where there are fields "pid" and "subject". So I assume there's a way to swap these 2? Could you tell me how to do it, please.