UPDATE query question

[gertjanmeire]

Hi there,

 

I'm currently trying to update a value in my DB, and it works great (I'm using PDO prepared statements btw).

Only problem is the number I'm inserting gets added to the current value that's in the DB, but I want it to have a MAXIMUM value of 100 or a min value of -100.

I want to check if the current value in the table + the value I'm adding will be higher then 100 or smaller than -100, if so I'd like it to have a value of 100 or -100.

 

This is my code so far: (the value I'm talking about is "mood_points = :mood_points)

function updateMoodAndUserPoints(){
        $db = Major4PDO::getInstance();
        $sql = "UPDATE users SET score_points = score_points + :score_points, mood_points = mood_points + :mood_points WHERE id = :id";

        $params = array(
            "score_points" => $_POST['score_points'],
            "mood_points" => $_POST['mood_points'],
            "id" => $_POST['id']
        );

        $stmt = $db->prepare($sql);

        if($stmt->execute($params)){
            return true;
        }else{
            return false;
        }
    }

 

Any help would be appreciated!

[Illusion]

UPDATE users SET score_points = score_points + :score_points,mood_points =  (
                                                                     case when mood_points + :mood_points > 99 then 100
                                                                                when mood_points+:mood_points < -99 then -100
                                                                      else mood_points+:mood_points end case )  WHERE id = :id

[gertjanmeire]

Thank you! This worked for me, although I had to change the syntax a little bit to make it work...

 $sql = "UPDATE users SET score_points = score_points + :score_points, mood_points =
        CASE
           WHEN mood_points + :mood_points > 99 THEN 100
           WHEN mood_points + :mood_points < -99 THEN -100
           ELSE mood_points + :mood_points
        END
        WHERE id = :id";

 

I did not know you could do this in SQL