Solved

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

Posted on 2008-06-26
6
938 Views
Last Modified: 2013-12-12
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
Comment
Question by:RetroRocker
  • 4
  • 2
6 Comments
 
LVL 20

Expert Comment

by:virmaior
ID: 21875597
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
 

Author Comment

by:RetroRocker
ID: 21876410
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
 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21876992
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:RetroRocker
ID: 21877308
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
 

Author Comment

by:RetroRocker
ID: 21890984
Now it's all the way there ! Thanks for the ideas, very helpful !
RR
0
 

Author Closing Comment

by:RetroRocker
ID: 31471008
You did a great job of pointing a beginner in the correct direction ;-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question