Forum Moderators: coopster
Im setting up a payment system for a clien who runs a news paper in san diego. And i need to have a SQL DB that stores the information that person puts in, but i need that after 30 days, if new paypment has not been made, then that person no longer has access to page 'x'. How could i go about doing that with php? Thanks alot in advance.
The following query selects all records with a date_col value from within the last 30 days:
mysql> SELECT something FROM tbl_name
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col;
So basically, you create a column for the user that records payment date (called "date_col" in the example). You might choose to do it a little differently like, for example
SELECT username, DATE_DIFF(CURDATE(), date_col) as days_since_payment WHERE username="jasonq";
Then in your program logic you can check for the number of days since payment and if greater than 30, say they are past due. If you do it this way, you can tell them how many days until payment is due or how many days past due they are.
In your situation, though, I would recommend another and more flexible way to do it. If you put in an expiration date rather than a payment date, then if someone prepays for 90 days, you could handle that situation. Also, it will be more efficient because you will only do date math upon UPDATE (which is less common) and not upon SELECT.
So when someone pays, you update a column called "expiration" like so
UPDATE INTO tblname SET expiration = DATE_ADD(CURR_DATE(), INTERVAL 30 DAY) WHERE username = 'jasonq';
Is that enough to get started with?
Tom
