Take the 2-minute tour ×

I'm hoping I can get some advice.

I have accounting ledgers in my PHP/MySQL system, with invoices that hit the ledger at intervals (for this example, let's just say once a month). I also have late fees that need to be applied after a certain date. The "posting" date of the invoice, as well as the "this is late" date are in the DB already.

I'm wondering which is a better method to run my script which applies a ledger item automatically (once a month) - and enters a late fee automatically (will also be once a month, if needed). For all users of the system, these dates are different - and like I said, all stored in the DB. My options as I see it are:

  • Have a CRON job that runs once a day (i.e. 12:00am each day) - and goes through every item, doing work if the date conditions are met.

    I think I like this method, because it takes care of everything all at once, at the same time every day.

    I am worried about this method because: (1) what if my server is down when my CRON job was scheduled, does CPANEL completely skip that day now? And (2) if I have 100k or even 100Million entries to sift through, will this be an unbearable load on my server whenever this is ran?

  • Call this script to "do work" if date conditions are met, only when that related user logs in.

    The reason why I don't like this method as much, is because it gets increasingly complex to tell how many invoices to apply, and how many late fees to apply. Further, I have an "administrator" user who can see EVERYTHING from EVERYONE - that user may have not up to date ledger info if the tenant hasn't logged in recently enough...

    The reasn I do like this method, is because it obviously is a less load on the server...

Perhaps I'm over thinking things. Not sure... but advice would be much appreciated. Thanks.

share|improve this question
What if user doesn't log in your system for a year - you will not send him invoice? –  zerkms Apr 12 '11 at 2:26
If you do it daily, do you really expect to have a bulk of 100million rows that need updating? For your second case, I'd recommend doing 2 things. 1. Calculating results when the admin sees a particular user. 2. If you have any stats on the page give the admin a button to sync data. Now because users might never return and because your admin might be lazy. You can try a combination of both your methods. P.S. As far as I know if the cronjob fails. It's "skipped". –  Khez Apr 12 '11 at 2:29
@zerkms - While I see your point, the fact is still I want the code to be ultimately robust... and going 2-3 months until getting serious about collecting the money is common... @Khez - I won't update 100Mil rows, but I WILL have to CHECK 100Mil rows... –  Shackrock Apr 12 '11 at 2:36
add comment

2 Answers

up vote 1 down vote accepted

I think the best bet is to use a cron tab, for all the benefits you mention. I would add to that though that if you use a transaction safe table, such as INNODB, and log the completion of the cron tab, if the table crashes during the crontab the changes will be reverted and you can check the log to see what dates had crashes so that you can manually make the changes. In fact, if you log the crontab task's completion in a table, you can use that log (grabbing how many days ago the script was run last) to automatically handle missed days.

As far as the server load, you probably won't have too much of a problem updating many records at once. The thing is that with a properly structured query, you should only be updating (or inserting) based on the records that have a date that matches your selected parameters. You shouldn't need to actually loop through every record in the db.

share|improve this answer
what do you mean by "transaction safe" table? I've NOT been using INNODB, because myisam is better for "tons of reads," from what I understand... (my solution has many more reads than writes). Further, I will still need to loop through every record to check dates, right? I can't get away from that. Of course I only need to Update where the conditions are met. –  Shackrock Apr 12 '11 at 13:26
add comment

I think the cron job is a good idea. However, either method you'd probably want to have some way of marking items (rows?) as "processed". Your cron job should look for "unprocessed" data rather than by date. This is, IMHO, safer and will solve the problem if the job fails to run for some reason.

I have a similar function in one of my apps. However, it's more of a queue style. A task gets put on the queue from any part of the application. A worker (could be triggered via cron) goes through the queue and completes the task and marks it as "processing" and then processed. If the next worker comes along and finds a task "processing", it checks on the worker to make sure it is not a zombie. If it is, it kills it off, then restarts the task. etc etc etc.

I actually have one master queue that can hold various types of tasks (email notifications, file updates, etc), then a worker processes the master queue into job specific queues(one for email, etc).

share|improve this answer
add comment

Your Answer


