Jump to content

Calculating Monthly Subscriptions


Lukeidiot

Recommended Posts

Okay guys, I figured out how to calculate monthly subscriptions into days, but I am stuck on calculating multiple monthly subscriptions.. for example

 

Heres my mysql table:

subsl.png

 

How would I calculate this:

Subscription Start: February 21, 2011, 12:00 am

Subscription End: April 22, 2011, 12:00 am

Subscription Left: 60 days

 

My trouble is that when the user buys another month, it calculates the end time based on the new "Subscription Start Date". Or vice versa.

 

Any ideas guys?

Thanks.

Link to comment
Share on other sites

So the date column is when the user signed up for the service, correct? The length is the amount of time from the date that needs to pass before the subscription is over, correct?

 

When the user buys another month, do you update the date column too?

 

When they buy another month, you just need to add 2592000 to the length column [where it would equal 5184000 for two months of service], and it would then show that from the original start date, they'd have two months of service.

Link to comment
Share on other sites

So the date column is when the user signed up for the service, correct? The length is the amount of time from the date that needs to pass before the subscription is over, correct?

 

When the user buys another month, do you update the date column too?

 

When they buy another month, you just need to add 2592000 to the length column [where it would equal 5184000 for two months of service], and it would then show that from the original start date, they'd have two months of service.

 

Yes correct, but I am adding a new row for each subscription, then using this to calculate:

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");
$sub_row = mysql_fetch_assoc($sub);

$sum = mysql_query("SELECT SUM(length) as total_length FROM rsp_subscriptions WHERE username = '$id'");
$subSum = mysql_fetch_assoc($sum);

$sql = "SELECT * FROM rsp_users WHERE username = '$id'";	  
$e = mysql_query($sql);
$row = mysql_fetch_assoc($e);

 

Its hard to explain but it seems to "reset" once a new month is added, I need to make it aware of the old dates with the new dates.

Link to comment
Share on other sites

Well then it appears I might need to see more code?

 

BUT, if you're adding a new row each time and getting the 'total_length' then you only need to get the FIRST row from the top query... that way when adding the total time, you're adding it to the first date of purchase, not the last date of purchase...

 

So you can either do

 

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC LIMIT 1");

 

That way you add the total length to one column, and that being the first.. but if you NEED All of the rows, you'd have to do an IF statement in a loop or something?

 

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");

if (mysql_num_rows($sub) > 0) {
for ($i = '0'; $i < mysql_num_rows($sub); $i++) {
	$data = mysql_fetch_array($sub, MYSQL_ASSOC);

		if ($i == '0') {
			$first_date = $data['date'];
			// Some more code here?
		} else {
			// Some code here?
		}
}
}

 

You can parse out each row from there if you'd like, but then after this, you'd add the total_length to the $first_date variable to get the end date.

Link to comment
Share on other sites

Well then it appears I might need to see more code?

 

BUT, if you're adding a new row each time and getting the 'total_length' then you only need to get the FIRST row from the top query... that way when adding the total time, you're adding it to the first date of purchase, not the last date of purchase...

 

So you can either do

 

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC LIMIT 1");

 

That way you add the total length to one column, and that being the first.. but if you NEED All of the rows, you'd have to do an IF statement in a loop or something?

 

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");

if (mysql_num_rows($sub) > 0) {
for ($i = '0'; $i < mysql_num_rows($sub); $i++) {
	$data = mysql_fetch_array($sub, MYSQL_ASSOC);

		if ($i == '0') {
			$first_date = $data['date'];
			// Some more code here?
		} else {
			// Some code here?
		}
}
}

 

You can parse out each row from there if you'd like, but then after this, you'd add the total_length to the $first_date variable to get the end date.

 

I think I tried this, it kept giving me negative subscription time when I was testing it out. For example I set one sub_start to Jan-1-2010, and the other to Jan-1-2011, and it was negative one year.

Link to comment
Share on other sites

Heres the script

 

<?php
include("connect.php");
include("datediff.php");

$id = $_GET['id'];

$sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");
$sub_row = mysql_fetch_assoc($sub);

$sum = mysql_query("SELECT SUM(length) as total_length FROM rsp_subscriptions WHERE username = '$id'");
$subSum = mysql_fetch_assoc($sum);

$sql = "SELECT * FROM rsp_users WHERE username = '$id'";	  
$e = mysql_query($sql);
$row = mysql_fetch_assoc($e);

//calculates subscription details
$today = date("F j, Y, g:i a");
$startDate = $sub_row['date'];
$endDate = strtotime($row['sub_end']);
$sub_endit = $subSum['total_length'] + strtotime($startDate);
$sub_endit2 = date("F j, Y, g:i a", $sub_endit);
$end_sub_length = strtotime($subSum['total_length']) + strtotime(date("F j, Y, g:i a"));
$sub_left = dateDiff($today, $sub_endit2);

$formatStartDate = strtotime($startDate);
$displayStartDate = date("F j, Y, g:i a", $formatStartDate);

mysql_query("UPDATE rsp_users SET sub_left = '$sub_left' WHERE username = '$id'");

echo "sub_left: " . $row['sub_left']; ?>
<br />
<b>Subscription Start: </b><?php if($sub_row['date'] < '0'){ echo "N/A<br>"; }else{ echo "$displayStartDate<br>";} ?>
<b>Subscription End: </b><?php if(strtotime($sub_endit2) < strtotime('December 31, 1969, 5:00 pm')){ echo "N/A<br>"; }else{ echo "$sub_endit2<br>";} ?>
<b>Subscription Left: </b><?php if($sub_left < '0'){ echo "N/A<br>"; }else{ echo "$sub_left days<br>";} ?>
<b>Status: </b><?php if($row['activated'] == 0){ echo " Unactivated";} else { echo " Activated";} ?>
    <br />
<?php

    $sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");

if (mysql_num_rows($sub) > 0) {
for ($i = '0'; $i < mysql_num_rows($sub); $i++) {
	$data = mysql_fetch_array($sub, MYSQL_ASSOC);

		if ($i == '0') {
			$first_date = $data['date'];
			echo $first_date;
		} else {
			// Some code here?
		}
}
}
?>
    
    
    

 

datediff.php

<?php
/**
* Finds the difference in days between two calendar dates.
*
* @param Date $startDate
* @param Date $endDate
* @return Int
*/
function dateDiff($startDate, $endDate)
{
    // Parse dates for conversion
    $startArry = date_parse($startDate);
    $endArry = date_parse($endDate);

    // Convert dates to Julian Days
    $start_date = gregoriantojd($startArry["month"], $startArry["day"], $startArry["year"]);
    $end_date = gregoriantojd($endArry["month"], $endArry["day"], $endArry["year"]);

    // Return difference
    return round(($end_date - $start_date), 0);
}
?>

 

 

 

 

I tried your script, but it still calculates 60 days even though the first Subscription was bought in "Jan-1-2010"

Link to comment
Share on other sites

My next suggestion would be to make sure your data is being sorted correctly.. I've had this problem many times. Everything else was right, but the data wasn't being sorted the way it should have...

 

Try running something like this:

 

<?php

    $sub = mysql_query("SELECT * FROM rsp_subscriptions WHERE username = '$id' ORDER BY date DESC");

if (mysql_num_rows($sub) > 0) {
for ($i = '0'; $i < mysql_num_rows($sub); $i++) {
	$data = mysql_fetch_array($sub, MYSQL_ASSOC);
			echo $data['date'] . "<br>";
}
}
?>

 

And just make sure the dates are actually sorted like they should be... other than that, idk

Link to comment
Share on other sites

You can do this directly in the query.

 

The following sample query uses the length in days instead of seconds -

 

$query = "SELECT username, MIN(date) as start_date, SUM(length) as length,
DATE_ADD(MIN(date), INTERVAL SUM(length) DAY) as end_date,
DATEDIFF(DATE_ADD(MIN(date), INTERVAL SUM(length) DAY),CURDATE()) as days_left
FROM rsp_subscriptions GROUP BY username";

 

This gives an output like -

 

Username: Luke, Subscription Start: 2011-01-11, Total Length (days): 60, Subscription End: 2011-03-12, Days left: 19

 

Username: stmiddleton, Subscription Start: 2011-01-18, Total Length (days): 1, Subscription End: 2011-01-19, Days left: Expired: 33 Days ago

 

Where the Luke username has two rows of 30 days each and the stmiddleton username has one row of 1 day.

 

The actual code I developed for this -

 

<?php
// subscriptions - (deposit account)

include 'db.inc.php';
$mysqli = new mysqli($db_host,$db_user,$db_pwd,$db_name);

// some dummy test data
$data[] = array('username'=>'Luke', 'sub_type'=>'1 Month', 'date'=>'2011-01-11', 'amount'=>'0.01', 'length'=>'30');
$data[] = array('username'=>'Luke', 'sub_type'=>'1 Month', 'date'=>'2011-02-01', 'amount'=>'0.01', 'length'=>'30');
$data[] = array('username'=>'stmiddleton', 'sub_type'=>'Free Trial', 'date'=>'2011-01-18', 'amount'=>'0.01', 'length'=>'1');

// code to insert dummy data, removed  ...

$query = "SELECT username, MIN(date) as start_date, SUM(length) as length, DATE_ADD(MIN(date), INTERVAL SUM(length) DAY) as end_date, DATEDIFF(DATE_ADD(MIN(date), INTERVAL SUM(length) DAY),CURDATE()) as days_left FROM rsp_subscriptions GROUP BY username";
if($result = $mysqli->query($query)){
while($row = $result->fetch_assoc()){
	if($row['days_left'] < 0){$row['days_left'] = "Expired: " . abs($row['days_left']) . " Days ago";}
	echo "Username: {$row['username']}, Subscription Start: {$row['start_date']}, Total Length (days): {$row['length']}, Subscription End: {$row['end_date']}, Days left: {$row['days_left']}<br />";
}
} else {
echo "Query failed: $query, Error: {$mysqli->error}<br />";
}
?>

Link to comment
Share on other sites

You can do this directly in the query.

 

The following sample query uses the length in days instead of seconds -

 

$query = "SELECT username, MIN(date) as start_date, SUM(length) as length,
DATE_ADD(MIN(date), INTERVAL SUM(length) DAY) as end_date,
DATEDIFF(DATE_ADD(MIN(date), INTERVAL SUM(length) DAY),CURDATE()) as days_left
FROM rsp_subscriptions GROUP BY username";

 

This gives an output like -

 

Username: Luke, Subscription Start: 2011-01-11, Total Length (days): 60, Subscription End: 2011-03-12, Days left: 19

 

Username: stmiddleton, Subscription Start: 2011-01-18, Total Length (days): 1, Subscription End: 2011-01-19, Days left: Expired: 33 Days ago

 

Where the Luke username has two rows of 30 days each and the stmiddleton username has one row of 1 day.

 

The actual code I developed for this -

 

<?php
// subscriptions - (deposit account)

include 'db.inc.php';
$mysqli = new mysqli($db_host,$db_user,$db_pwd,$db_name);

// some dummy test data
$data[] = array('username'=>'Luke', 'sub_type'=>'1 Month', 'date'=>'2011-01-11', 'amount'=>'0.01', 'length'=>'30');
$data[] = array('username'=>'Luke', 'sub_type'=>'1 Month', 'date'=>'2011-02-01', 'amount'=>'0.01', 'length'=>'30');
$data[] = array('username'=>'stmiddleton', 'sub_type'=>'Free Trial', 'date'=>'2011-01-18', 'amount'=>'0.01', 'length'=>'1');

// code to insert dummy data, removed  ...

$query = "SELECT username, MIN(date) as start_date, SUM(length) as length, DATE_ADD(MIN(date), INTERVAL SUM(length) DAY) as end_date, DATEDIFF(DATE_ADD(MIN(date), INTERVAL SUM(length) DAY),CURDATE()) as days_left FROM rsp_subscriptions GROUP BY username";
if($result = $mysqli->query($query)){
while($row = $result->fetch_assoc()){
	if($row['days_left'] < 0){$row['days_left'] = "Expired: " . abs($row['days_left']) . " Days ago";}
	echo "Username: {$row['username']}, Subscription Start: {$row['start_date']}, Total Length (days): {$row['length']}, Subscription End: {$row['end_date']}, Days left: {$row['days_left']}<br />";
}
} else {
echo "Query failed: $query, Error: {$mysqli->error}<br />";
}
?>

 

Very nice work!

 

I have just one last question though, lets say a users subscription has been expired for 45 days and he buys another 30 days subscription, how can I make it "know" to add 30 days and not 30 - 45 days?

 

P.S. I am using Seconds instead of Days, is it possible to use Seconds directly in the mysql query?

 

Thanks so much

Link to comment
Share on other sites

he buys another 30 days subscription, how can I make it "know" to add 30 days and not 30 - 45 days?

 

I would treat it as a completely separate subscription/account. You would need to add a column for a subscription/account id rather than using the username column.

 

is it possible to use Seconds directly

 

Yes, just alter the INTERVAL SUM(length) DAY to INTERVAL SUM(length) SECONDS (two places in the query.)

Link to comment
Share on other sites

he buys another 30 days subscription, how can I make it "know" to add 30 days and not 30 - 45 days?

 

I would treat it as a completely separate subscription/account. You would need to add a column for a subscription/account id rather than using the username column.

 

is it possible to use Seconds directly

 

Yes, just alter the INTERVAL SUM(length) DAY to INTERVAL SUM(length) SECONDS (two places in the query.)

 

Ah yeah thanks man!

 

Do you think you could explain this better?

I would treat it as a completely separate subscription/account. You would need to add a column for a subscription/account id rather than using the username column.

 

I have rsp_users, and rsp_subscriptions already and update both accordingly. I put "sub_left" (in days) on rsp_users, and calculate with your query on rsp_subscriptions. How would I go about expiring an older subscription?

 

Thanks!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.