I'm in need about a MySQL query which happens to be far more complicated than any other I have ever made (I can barely use the simpliest queries really).
So, I have 2 tables. One has the columns (user, rank), the other has (user, vote). In a PHP environment, I need to update the rank of a user in table1 based on the vote contained in table2. Something along the lines of "If user voted 1, add +50 to his rank. If user voted -1, add -50 to his rank. If user voted 2 add +200 to his rank."
I can do all the "if" work in PHP but would really need to have an efficient SQL query to get the data and manipulate the rank, because I'm already full of poorly optimized interrogations in my project.
Thank you so much in advance!
Using an insert...select statements combined with an ON DUPLICATE KEY UPDATE clause, you should be able to do it in one go:
INSERT INTO userrank(user, rank)
SELECT v.user, sum(v.vote) * 50 as rank
FROM uservote v
ON DUPLICATE KEY UPDATE rank = v.rank
You could add a second statement, in case you want to remove a user's rank when all his votes are deleted:
DELETE FROM userrank r
WHERE NOT EXISTS (SELECT 'x' FROM uservote v WHERE v.user = r.user)