insert monthly

[mrhankey]

hi,

i am adding in a basic accounts table to my system. what i need to know is what is the best way to insert repeat payments. such as rent monthly?

many thanks

[adam_k]

I think you need to share more info before any answer you receive is really helpful.
What is your system (web, windows), what is your db (MS SQL, MySQL, Oracle, Access, Excel, flat file) for starts.
By rent monthly do you mean you've got amount of rent, account and number of months to insert?
First option would be in the app side to loop for i = 1 to number of months and either insert in each loop or add values to a single insert. Second option could be to feed a stored procedure this info and loop there.

[mrhankey]

hi,

mysql is the database, i just want to have it where a user inserts on my php page the rent amount and selects monthly as payment frequency. then the database auto inserts that amount every month as a new record with a timestamp so that they can look back at the end of year and see rent they paid for the year and other bills.

thanks

[adam_k]

Then I guess you need to create a table with tasks.
On the top of my head I assume you are going to need job id, account to get the money, account to transfer the money, ammount, job status, day that the job needs to run, date of last processed payment, optionally frequency (in case you want to make payments weekly or every couple months).
Then you create a job that runs probably every night - or once per day, reads the active jobs that need to run on x day everymonth - or if weekly x day every week- and check when you paid that job last time.
The jobs that qualify can be processed, with a couple insert I guess and a couple or more updates, depending on your schema.

[mrhankey]

yeah do you have an example of a cron job that would run and do a check and insert?
cheers

[adam_k]

Scheduling a MySQL job with CRON
As for the check and insert, shouldn't you write it according to your db schema?