Site Membership Expiration Date?

This is my first time using MySQL and I want to create a membership site. This is the table I outlined so far:

user_id
username
password
email
registration_date
subscription
membership_date

When users of my site pays for a month of subscription, I want to set membership_date to date when they subscribed. Then after that, do I check if their date has expired every time they log in?

What I thought of doing is maybe perform a check when they are logged in and visit the site to see if membership_date + 31 is greater than today’s date, if so, then deactivate membership by changing subscription to false.

Something like this…


if membership_date  + 31 > today's date
then assign FALSE to subscription

How can I find out the common and secure way to go about this?

You could set up a cron job, It would check in specified interval if any of the users have subscription expired and update records if they do. Perhaps per hour or per day should be enough.

Your approach also works fine.

You can also take a look at some modules from open source CMS solutions that provide subscription functionality and learn from their code.

I would recommend you to use the condition in the query itself:


SELECT tm.*,
	(CASE 
		WHEN CURRENT_DATE < DATE_ADD(tm.membership_date, INTERVAL 31 DAY) THEN 'YES'
		ELSE 'NO'
	END) AS expired_flag
FROM tbl_members AS tm
WHERE username='yourpassword' AND `password`='yourpassword';

Now you can check in PHP whether the logged in user’s date has expired or not.

Either you can do it in PHP itself, see strtotime() for this.