Solved

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

Posted on 2008-06-26
6
939 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

856 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