Iterating through / decrementing all values in a MySQL table column using PHP ?
Posted on 2008-06-26
Given a MySQL table of registered users which contains many columns one of which is for their current remaining days of subscription (call it 'subs' and it's SMALLINT UNSIGNED). I would like to find out the most suitable way to iterate through this column and decrement each value by 1. This script would be run as a cronjob once per day to countdown remaining subscription time. I can think of various ways of achieving this but what would you experts do ? for, for each, while loops ?
I guess I could start along the line of SELECT subs from users, perhaps assigning the result of this query to an array, then using a foreach loop to decrement and UPDATE to post the new value ?
Each row in the users table also has a unique numerical ID, so perhaps I could select each row and decrement the subs value in an UPDATE query while iterating through the user IDs ?
I would be grateful for suggestions and perhaps code snippets or links to for what might be regarded as the 'best' way forward with this. I suspect it is far from an onerous task for someone familiar, perhaps 4 or 5 lines of code, but as a beginner I am just unsure which way to go and not knowledgeable enough !
my thanks in anticipation