-1

I have two tables

Users:

user    |  name  |  country |  rank              | country_rank       | points
1       |  frank |  US      |  to be determined  | to be determined   | to be determined

Awards:

awarded_to  |  points_awarded  
1           |  10
1           |  30

How can I make a stored procedure to update the users total points based off of the points from awards, then their rank and country_rank respectively based off of the order of the points (i.e. rank 1 would be the user with the most points)?

I considered making a PHP script and using a crontab to call it occasionally that would just select the info and do the math etc in PHP, but stored procedures seems much more practical for my use-case.

whocodes
  • 331
  • 1
  • 3
  • 4
  • can you provide a fiddle ? What have you tried ? – Gonzalo.- Oct 24 '15 at 03:38
  • do a switch based upon column selected. – i am me Oct 25 '15 at 05:31
  • Is there no time stamp column in the table Awards? In your example, is the points awarded to user 1, 10? 30? or 10+30? (you don't make the expected result clear) – Paul Maxwell Oct 25 '15 at 05:32
  • @Used_By_Already The amount of points awarded is dependent on the action performed, it could be any amount. – whocodes Oct 25 '15 at 20:09
  • Thanks for that but please re-read the comment. **Which result** are you **expecting** for your sample? It is your expectation that you have not stated in the question, There was another question too. Is there a timestamp (or any date/time value) in the Awards table? – Paul Maxwell Oct 26 '15 at 00:17

1 Answers1

0
create temporary table awardsum (user int, total int); #temp
insert into awardsum
select a.awarded_to, sum(a.points_awarded) 
from users u 
inner join awards a on u.user=a.awarded_to
group by a.awarded_to;

update users
join awardsum on users.user=awardsum.user
set users.points = awardsum.total;    

SELECT @row:=0;
UPDATE users
SET rank = (@row:=@row+1)
ORDER BY points desc;

drop table awardsum;
mehmet mecek
  • 2,615
  • 2
  • 21
  • 25