Horancorn Horancorn - 6 months ago 9
MySQL Question

Recurring mysql insert

I have php form in which you can submit your income / expenditure (basic home budget management). In order to have them auto repeat every month I use code as below

INSERT INTO income (ammount, date, desc, category)
SELECT ammount, now(), desc, category FROM income WHERE recurring=1 and date LIKE '____-__-23'


Recurring is always 0 or 1(tinyint) to differentiate one time from recurring income/exp. It all works fine except for the fact that i have 62 php scripts added to cron (31 for income and 31 for expenditure - 1 each for each day of the month). Is there any better solution to that ? Not looking for complete solution - just point me into right direction. Thanks.

Answer

There's a function called DAY (synonymous to DAYOFMONTH), which extracts the day of month from the date field. This means that you can change

 date LIKE '____-__-23'

to

DAYOFMONTH(date) = DAYOFMONTH(NOW())

You will no longer need one script for each day of the month.

Remember not to run those queries just before midnight.

If you want to learn more, read the docs.