Automatic Subscription Renewal Notification

[reandre68]

Hi all (again)

Right now I'm trying to find something that will enable me to receive notifications when my clients' subscription is about to expire. I have a database in MySQL with all the necessary details. Maybe some sort of diary system or something else. I can't find anything substantial in Google or anywhere else for that matter. Maybe somebody here has a suggestion or a link that could help me?

I appreciate it.

Reandré

[Guest]

If you have MySQL database with "all the necessary details" then simply setup a query where if expiration date = now then send an email or notify you from your admin section, etc. If you don't have expiration date in your database then add that necessary detail to your databse table.

[reandre68]

Thanks for the quick reply. I have no idea how to structure such a query. Do you maybe have a link or something to a site that can teach me to do that? Meantime I will keep searching and post here if I find something.

[Guest]

I haven't had a chance to test the query but this should work:

<?php
mysql_select_db($database_reandre68, $reandre68);
$query_subscription_expired = "SELECT user_email FROM subscription_table WHERE expiration_date < NOW()";
$subscription_expired = mysql_query($query_subscription_expired, $reandre68) or die(mysql_error());
$row_subscription_expired = mysql_fetch_assoc($subscription_expired);
$user_email = $row_subscription_expired['user_email'];
$subject = "Subscription Expired";
$message = "Hello! Your subscription has expired.";
$from = "youremail@yourwebsite.com";
$headers = "From: $from";
mail($user_email,$subject,$message,$headers);
?>

Then just visit the page that contains the script to run the query. You can automate the process of running the query daily through automator or Applescript, etc. You should also add a field like notified or something in your database and update the notified field upon running the query so that clients aren't notified more than once.

[reandre68]

I'm testing it right away. Will let you know what happens.

Thanks a lot!!!

[reandre68]

What syntax would I use if I want to change WHERE expiration_date < NOW() to WHERE expiration_date < (one month/one week prior)?

[reandre68]

Maybe I should ask how do I set an expiration date? I didn't think of that when I said "all necessary information". I have a column with CURRENT_TIMESTAMP option for when user subscribes. But obviously if I want to use WHERE expiration_date < NOW() I have to have a column that sets an expiration date based on the option the user selected (3 months, 6 months, 12 months). I have a column that uses the value of the subscription period but in varchar. I'm baffled. Please can you explain to me how to do this if I add a column expiration_date?

[bregent]

If you are storing a timestamp of the subscription date and also store the subscription period, then you don't need to store the expiration date. Just use simple date math to add the subscription date to the period to calculate the expire date.

>I have a column that uses the value

>of the subscription period but in varchar

This is why you need to be careful when designing a database. Columns that will be use in calculations should be stored as numeric types. Depending on how the value is stored you can use a SQL data type conversion function, or you may have to use logic in a script.

[reandre68]

Hi bregent,

thanks I understand that, but how do I actually do it? In which datatype format should my column for application period be and the also the value? I am using a simple form to submit the data, the value for the radio buttons are 3 Months, 6 Months, 12 Months. But in what format should that value be to go with what datatype? Something like DATE datatype maybe? Then what should the values be for the radio buttons instead of just a varchar 3 Months or 6 Months or whatever?

thanks

[reandre68]

Feels like I'm not making sense even to myself. Sorry about that. What I wanted to say was that I need a column for the expiration date, so in the form the user uses to subscribe, they have 3 options with simple radio buttons. 3 months, 6 months or 12 months. I already have a column for the current timestamp that the user subscribed where the default value is set to 'CURRENT_TIMESTAMP' so when a user selects an option from the three radio buttons, I need the table to automatically add an expiration date based on the option selected like it adds the current timestamp. For date_subscribed the default value is 'CURRENT_TIMESTAMP' so what should I do to add 3/6/12 months based on the selection? Hope I make sense this time

[bregent]

>What I wanted to say was that I need a

>column for the expiration date,

No you don't. You are already storing the subscription start date and the subscription length. It is generally a violation of normalization rules to store calculated columns so unless you have a compelling reason to do so, you should not.

[reandre68]

Sorry bregent,didn't refresh my page before posting so didn't see your message coming in. Yes I do already have a column to store the subscription length but as I said it's in text format. To minimize/eliminate complicated coding, I want to "pre-calculate" the expiration date so it gets inserted into my table already as the date my query should use to check if the subscription is near expiry. There should be a way to do this but I am unaware of anything and if there is a better way please can you help me out by letting me know what it is?

[Guest]

bregent has the right idea with regard to using math to determine expiration. I didn't know you had different expiration periods so for my database table logic I would have a table for the expiration date anyway (juuust in case cause you never know). Anyway, on your query page that has the code I gave you earlier you would first make a query for the registered date and, presuming you're using php, use php math to determine the expiration date. Then run the query with the variable of the expiration date. Oh and for date data you should always use the date charset in your database IMO and use scripting to format the date or apply math, etc.

Make sense?

Oh and about that notified clause mentioned earlier the query would actually be something like this:

SELECT user_email FROM subscription_table WHERE expiration_date < NOW() AND notified = 0

Then after running the mail script run another query similar to the first but this time it UPDATE (s) the database and SET (s) the notified field to a value of 1 for expired users so that after they're sent a notification email their notification status is set in the database. I can write you up another untested example real quick if you need one - it's really just a simple UPDATE query. This will prevent the user from being emailed daily when the query is run. On your update subscription page that updates the users subscription run another query to reset the notified table field back to a value of 0 to restart the process of expiration notification.

Hopefully that all makes sense.

[reandre68]

It makes a lot of sense. Only I don't know how it's implemented. That's what I'm doing here trying to learn. Your help to me is priceless. You don't have to write me a script, although a quick example would be nice. I am figuring this out bit by bit and making progress. I don't just want to copy someone's work and use it without knowing what's actually happening "behind the scenes". So please can you explain as you go (in human language. LOL.) that would be great if it doesn't cause too much trouble. If possible also explain what's happening in the first script? I understand most of it, but maybe someone else comes along in a few days then this could help him *wink wink*

Thanks iPHP

[Guest · Accepted solution]

query explanation:

The first script supplied basically says find everyone in your database that expires yesterday and get their email address then email them a notification from you saying that their subscription is expired. The addition I mentioned in the last post would set your database to let you know that the users that have been notified have been notified. So after the end of the earlier script place a query like this:

$query_notification = "UPDATE subscription_table SET notification = '1' WHERE expiration_date < NOW()";
$notification = mysql_query($query_notification, $reandre68) or die(mysql_error());

Just something I slapped together real quick. Untested again you know so consider the disclaimer sent.

[reandre68]

Hi,

So sorry I couldn't get back to you sooner. My internet was down due to cable theft. WTF right? Only one place where that could happen. Welcome to South Africa.

But thanks a lot for your  help  on the matter. I appreciate it a lot. Hope you have a great day.

Regards

[reandre68]

Can I maybe add a function into the radio button values that would set the expiry date to 3/6/12 months after current timestamp based on selection? I can't find anything on google about this or maybe I'm not using correct search terms but maybe someone knows? I don't know if this is possible? But instead of normal text saying 3 Months when someone clicks on the 3 months option radio button, can't I do something that will set the 3 Months to CURRENT_TIMESTAMP + 3 Months so it gets added as DATE datatype into expiry_date column?

[reandre68]

Can I maybe add a function into the radio button values that would set the expiry date to 3/6/12 months after current timestamp based on selection? I can't find anything on google about this or maybe I'm not using correct search terms but maybe someone knows? I don't know if this is possible? But instead of normal text saying 3 Months when someone clicks on the 3 months option radio button, can't I do something that will set the 3 Months to CURRENT_TIMESTAMP + 3 Months so it gets added as DATE datatype into expiry_date column?

In my code the value that gets inserted looks like:

GetSQLValueString($_POST['book_period'], "date")

Is there maybe an if statement to use? Something like if  selected value = 6 months then CURRENT_TIMESTAMP + 6months? I have tried some but couldn't get it to work.