Without using PHP or anything. Is there a way to do something like this:
UPDATE myTable SET var1 = var1 + 1 IF var1 < 5 ELSE var1 = 0 WHERE [WHERE clause]
Never really done any IF mySQL things so a little unsure?
So the result would be that the variable would never be higher than 5 and reset to 0 if its at 5 when incremented.
TIA
Either use IF()
UPDATE MyTable SET var1 = IF(var1 < 5, var1 + 1, 0) WHERE id = 1;
or in this case, simpler, just use the modulo operator
UPDATE MyTable SET var1 = (var1 + 1) MOD 6 WHERE id = 1;
Edit: Yes, I realize that if the range starts outside of 0..5 the latter one will not work, I just figured the intent was to keep the numbers between 0 and 5 inclusive. If I'm wrong about that, keep to the first one :)
You can do it with the if() function:
UPDATE myTable SET var1 = if(var1 < 5, var1 + 1, 0) WHERE [WHERE clause]
Try this:
UPDATE myTable SET var1 = IF( var1 < 5, var1 + 1, 0 ) WHERE ...
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if
Another possibility is to have two update statements:
UPDATE myTable SET var1 = var1 + 1 WHERE [WHERE clause] AND var1 < 5;
UPDATE myTable SET var1 = 0 WHERE [WHERE clause] AND var1 > 5
Note that I'm using var1>5 instead of var1>=5 in my second example, because columns with value 4 before the first statement will already have been updated to 5 and we don't want those to be reset to zero.