• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 960
  • Last Modified:

Iterating through / decrementing all values in a MySQL table column using PHP ?

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

RetroRocker
0
RetroRocker
Asked:
RetroRocker
  • 4
  • 2
1 Solution
 
virmaiorCommented:
well really you shouldn't have to do this... you should just use a DATETIME and compare.

but to do what you want:

UPDATE table SET subs = subs - 1 WHERE subs > 0;

no need to select.
0
 
RetroRockerAuthor Commented:
virmaior : thanks for returning this so soon. I really accentuates what I said about finding the 'best' solution ... you have come back with something I never even considered ie. DATETIME and compare !
This in itself begs continuance though because, as it didn't seem relevant to my line of thinking at the time, I did not mention that the relevant user's 'subs' value will be queried by that users copy of a desktop client app. which will display remaining subscription time, each time they execute it. This sort of thing is fairly common as I'm sure you are aware.
  So given all that, it seems I would need to write a query, to be sent by the desktop top client, which would compare current server time with the value of 'subs' for a user, convert the difference to days and display it. This looks good because it removes the need for a cronjob ! If you have any tips for the query I would always be happy to see them otherwise it will probably mean considerable  'experimentation' at my level :) and given your experience, can you think of any particular pitfalls or things to watch out for ?
thanks RR
0
 
virmaiorCommented:
i would just do the following:
(1) make one of the fields of the table expiration_date DATE_TIME
(2) when adding to that table insert the proper value

For the SELECT
SELECT * FROM subs WHERE NOW() > expiration_date
to find all of the users whose subscriptions have expired

if you are trying to catch them before that, then you would use  the DATE_DIFF function:
SELECT * FROM subs WHERE NOW() > DATE_SUB(expiration_date, INTERVAL 3 DAYS)

assuming you wanted to tell them three days in advance
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html for more details about the DATE_SUB function
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
RetroRockerAuthor Commented:
Yes this really does seem at least 99% of the way there ! Just give me a little time to play around with it (especially the desktop client query submission ... but that's not really this department) but I have no doubt I'll be back fairly soon to close it down and do the points thing.
If you think of anything in the mean time which may be useful, do please add it but otherwise thank you for the ideas.
RR
0
 
RetroRockerAuthor Commented:
Now it's all the way there ! Thanks for the ideas, very helpful !
RR
0
 
RetroRockerAuthor Commented:
You did a great job of pointing a beginner in the correct direction ;-)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now