PHP Membership Cronjob

[echo-tech]

Hi,

 

I have a question...

 

I have created a PHP system where you can purchase various membership systems (e.g Bronze, Silver and Gold). When the membership is purchased it updates the following:

 

Sets "package_bronze" to "1" 

Inserts date and time + 1 month into "bronze_exp_date" (e.g today is 16/07/2013, so it'll set the exp date to 16/08/2013)... 

 

I want to setup a cronjob so that every 5 minutes it'll run a PHP script to tell the database whether or not the users membership should have been set from "1" back to "0". 

 

I need some help with writing that code... 

 

I'm assuming it would be something like:

 

If a certain user's expiry date has passed then set the certain package variable date to 0 ...

 

Could anyone please help me with the mysql query for it and the php "if" statements?

 

Regards,

Echo-Tech 

[fastsol]

$today = date("d/m/Y");
mysql_query("UPDATE `table` SET `column` = 0 WHERE `expire_date` < $today");

I did a little assuming on the $today and expire_date, this may not work correctly depending on the format you have the date stored in the db.  If it's in a DATE format it might work right that way, otherwise I suggest reading up on the mysql date formats and queries for such things.

[AbraCadaver]

I can't really see this being useful. Why not just wait until the user wants to login and check the expiry?

 

I can only see this as being useful if another system is accessing the data, but then it can check the expiry as well if you control that system.

 

Just a thought.

[echo-tech]

Thanks fastsol I see how that works now...

 

I can't really see this being useful. Why not just wait until the user wants to login and check the expiry?

I can only see this as being useful if another system is accessing the data, but then it can check the expiry as well if you control that system.

Just a thought.

 

I suppose, but is there absolutely no way that the user can bypass the expiry date check? I want the system to be fast, if the user logs in and has 3 expiry dates to each package (for example) then it's going to take a while for the user to get logged in, not too long but it'll be long enough to be annoying after each check has to be complete...

[fastsol]

It should seriously only take maybe an extra 5th of a second for the server to run the queries, I wouldn't worry about it.

[echo-tech]

Okay thanks,

 

So would you recommend not using this cronjob unless in the future I maybe wanted to add a feature to email users deals if they don't have the membership package, or maybe a reminder that it's expiring etc.. ?

[AbraCadaver]

Thats up to you. I would only run it if I was sending reminders or deals.

[kicken]

Even if you add a separate email task you still don't really need to add a cron job just to check the expiry date. You also don't really need separate 1/0 and date fields for each level. Just allow the date field to be null. Then you could check these conditions

 

if bronze_exp_date = null then they never had a subscription

if bronze_exp_date is a date, but expired then they had a subscription that expired

if bronze_exp_date is a date and not expired then they have a current subscription.

 

You can use a CASE statement to query pseudo-columns for this information:

SELECT
  CASE WHEN bronze_exp_date IS NULL THEN 1 ELSE 0 END as hasHadSubscription
  , CASE WHEN bronze_exp_date < CURDATE() THEN 1 ELSE 0 END as hasSubscriptionExpired
FROM blah

For convenience you could wrap that up into a VIEW and use that view anywhere you want to query such information.