Forum Moderators: open
I am using php and mysql.
I cant work out how to do the following:
I have a members table that contains an entry to show if the member is valid or not (it contains 0 or 1)
When a new member signs up this entry is set to 1
After 1 year this entry needs to set to 0
I expect I will need to use a cron job to check the database everyday.
So, how do I set the date the new member signed up and how do I check if this date is 1 year ago and change the member status entry to 0
The reason I need this is because membership will need to be paid for and after 1 year the member will need to repay the membership fee.
When non members search the members I need to display whether the member is current or lapsed.
I will need to send reminder emails to members who need to renew.
Any help or links to tutorials or examples will be appreciated.
Thanks
I apologise if this should be in the ' PHP Server Side Scripting ' topic
you should read up on the MySQL Date and Time Functions [dev.mysql.com].
something like this should work:
UPDATE members SET valid=0 IF DATEDIFF(NOW(),lastpaid)=365
you might want to work out the process so that a member is notified and has a chance to renew before invalidation occurs.
Also, consider leap years . . . or the cron doesn't run one day for whatever reason, so entries may get skipped . . . . I'd use <=. :-)
update members set valid=0 where lastpaid <= date_sub (curdate(), interval 1 year);
(use curdate() if it's a date field '0000-00-00', now() for a datetime field '0000-00-00 00:00:00')
Second, I'd approach this a little differently. If a member signs up and it's a year membership, you should set an expiration date field. Then your query is just
update members set valid=0 where ExpirationDate >= curdate();
By using an expiration date field, you can know the status of your members at any time, extend membership manually by simply changing the expiration date field, and do things like
$expire = 5;
$select = "select rec_id,fname,lname,email from members where ExpirationDate = date_add(curdate(), interval $expire day)";
Then within that while loop, send an email, "your account expires in $expire days";
Of course, using = is subject to the same potential problem I originally mentioned - so I'd probably want to use a date_sent field so I know that member was sent the email - and it allows me to do this, if the cron crashes one day:
$select = "select rec_id,fname,lname,email from members where ExpirationDate =< date_add(curdate(), interval $expire day) and date_sent='0000-00-00'";
Once the email is sent, you do
update members set date_sent=curdate() where rec_id=$record_id;
Which prevents the member from being sent five emails, one a day.
Of course, you'd want the default value of date_sent to be 0000-00-00 or you'd also have to test against NULL.
