View Full Version : Increase non primary field

11-15-2011, 09:53 AM

I'm currently building a site for an online game and am almost complete. I have however run in to a bit of a wall. Players of the game can earn experience points and at certain point amounts they go up a level. What I want to know is this - is there a way to have a field (level) increase when another field (exp) reaches/passes a certain number?

Thank you in advance.

11-15-2011, 01:59 PM
you can use a TRIGGER to do that.

11-15-2011, 04:14 PM
i did find a mention of that after google but couldnt work out how to do it. any help is appreciated

Old Pedant
11-15-2011, 11:25 PM
I wouldn't bother with a trigger.

You can do this all in your SQL UPDATE statement.

First, create a LEVELTABLE that holds the level numbers versus point ranges.

CREATE levelTable ( level INT, levelName VARCHAR(30), minPoints INT, maxPoints INT );

table contents example:

level levelName minPoints maxPoints
1 private 0 99
2 corporal 100 399
3 sergeant 400 799
4 lieutenant 800 1499

And then, when you add points to user, do something like this:

UPDATE usertable AS U, levelTable AS L
SET U.points = U.points + $addedPoints,
U.userlevel = L.level
WHERE (U.points + $addedPoints) BETWEEN L.minPoints AND L.maxPoints
AND U.userid = $currentuser

See? Do it all in one step.

11-16-2011, 10:45 AM
thank you that worked