Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-26
6
Medium Priority
?
957 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 2000 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This article discusses how to implement server side field validation and display customized error messages to the client.
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to count occurrences of each item in an array.
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…

705 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