Solved

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

Posted on 2008-06-26
6
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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.
Many old projects have bad code, but the budget doesn't exist to rewrite the codebase. You can update this code to be safer by introducing contemporary input validation, sanitation, and safer database queries.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

696 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