Increase non primary field

Hi,

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.

you can use a TRIGGER to do that.

i did find a mention of that after google but couldnt work out how to do it. any help is appreciated

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.
Example:

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
etc.


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.

thank you that worked