CODEWITHSUNDEEP
phpmysql
James Clifton
James Clifton

Reputation: 175

Mysql formula to work out how many points they have according to value in a column

I have done this the long and hard way but I know it can be done quicker and easier ;)

If I have a column called 'puzzle_level' I wish to have 10 points for each record that is puzzle_level=5, 25 points for each record where puzzle_level=6 and 50 points if puzzle_level=7;

Quick edit, I actually need a varialbe total in PHP, so it would be $total_points = $sql_formula

Upvotes: 0

Views: 45

Answers (2)

Zane Bien
Zane Bien

Reputation: 23125

You can simply do it with a conditional CASE expression:

SELECT 
    points + 
    CASE puzzle_level 
        WHEN 5 THEN 10 
        WHEN 6 THEN 25 
        WHEN 7 THEN 50 
    END AS totalpoints
FROM your_table

This adds an amount of points based on the puzzle_level to the total point amount in the points column.

Then, simply reference the 'totalpoints' column in PHP.

Upvotes: 3

hjpotter92
hjpotter92

Reputation: 80639

UPDATE `table`
SET `points` =
  IF `puzzle_level` = 5 THEN 10
    ELSEIF `puzzle_level` = 6 THEN 25
    ELSEIF `puzzle_level` = 7 THEN 50
  END IF

Upvotes: 2

Related Questions