0

How do I set points based on whether the person was 1st, 2nd, or 3rd (per game)?

In my 'players' table I have the data of players id, name, and points(currently 0 point to everyone). My 'games' table includes the game id, game name, 1st_id, 2nd_id, and 3rd_id (all the last 3 ids match with 'players' id from the first table. Both already have data inserted already. my plan to the score system was to add points based on how many times a participant was in the top 3. 1st place worth 3 points, 2nd place worth 2 points, 3rd place worth 1 point. So for example if John won the 1st place in running and 2nd place in ice skating he would have 5 points total. How do I set the points in the 'players' table to achieve this?

6th Mar 2020, 1:08 PM
Killer Queen
Killer Queen - avatar
3 Respostas
+ 2
I imagine you'd probably need to run two queries to achieve that. The first query updates the 'games' table, updating either '1st_id', '2nd_id', '3rd_id' column, or all three of them. The second query updates the 'players' table, updating the 'points' column - depending on the player's rank (which decides the amount of points earned). Just my imagination though ; )
6th Mar 2020, 1:41 PM
Ipang
+ 2
You need a programming language to sum the point before updating. https://www.sololearn.com/learn/SQL/1850/ https://www.sololearn.com/learn/SQL/1869/ something like this: int p1s = 0, p2s = 0, p3s = 0; array ids = SELECT id FROM players array 1st = SELECT 1st_id FROM games for loop id in 1st_id switch(id) case ids[0]: p1s+=3;break; case.... array 2nd = SELECT.... for loop.... switch .. +=2 array 3rd = SELECT.... for loop.... switch.. .... +=1 UPDATE players SET points = p1s WHERE id = id[0] UPDATE... UPDATE....
6th Mar 2020, 2:03 PM
Gordon
Gordon - avatar
+ 1
Thank you both so much! :)
6th Mar 2020, 2:17 PM
Killer Queen
Killer Queen - avatar