Hello everybody.
I am really looking for guidance and advice here. I have just built a members only community site and now want to make certain areas of the site ‘paying members only’ areas.
I am not sure of the correct way to structure the SQL/MySQL/PHP so that members will only gain access to the restricted areas once payment has been made and that this access will automatically terminate at the end of the payment cycle.
I have put forward my ideas below but I am not sure if this is the best and most efficient way to do it; so, I would love to hear back from everybody with their ideas and guidance on the correct way to build this function.
I will be using Paypal as the preferred method of payment.
My proposal
First- we need a method to be able to determine a members user level . i.e one level will allow only for general access and the next ‘higher’ level will allow for access to restricted areas.
Below is the column that will be held in the members table and will be used to qualify a members membership level.
user_level TINYINT (1) UNSIGNED NOT NULL DEFAULT 0,
On receipt of payment an SQL function would automatically upgrade a person’s status to the higher level.
I propose to then have a payments table. This table will have three columns.
- The user ID
- Date of payment
- Date payment expires
I am thinking of storing the dates as Unix time stamp e.g. UNIX_TIMESTAMP(now())
The advantage of using a Unix time stamp, in my view, will be that it will be easier for me to calculate the end date for the access period. I.e for a 30 day access period I will simply MULTIPLY a 24 hour UNIX TIME STAMP BY 30
I.e
I.E 86400(24 hours) x 30 = 2592000 ) . I will then ADD THE 2592000 to the current Timestamp ( UNIX_TIMESTAMP (now()) )and this will give me the exact time 30 days, from the moment of payment, when the membership will expire.
I will then have an automated CRON JOB script that uploads every hour to check if a member’s membership has expired.
i.e
If CURRENT TIME is greater than the time stamp in the ‘Date payment expires’ column then the script will automatically change the status of USER LEVEL from HIGHER LEVEL TO LESSOR LEVEL
Please let me know what you think. I am still new to all this and this is my first real attempt to work out how to build a script to do something.
Warm regards
Andreea