how to update a users collumn based on the time returned by a countdown.

[alexandre]

i  am making this subscription system which will be based on a remaining time collumn , and i also  have this sub status  and sub_date as collumns. what i want to do is to update the sub_time_rem collumn at some point for the countdown to actually "count down" because the countdown itself is based on the sub_time_rem.

should i just be updating the sub_time_rem once a day based on the difference between two dates or there is a way to do this without hammering the database server with constant updates of the database ? even there i am unable to get this work the closest i got, was to have the countdown displaying a month left but without ever going down since its based on the remaining time of their subscription. if the remaining time doesnt get updated then the time left wont change ..

if anyone got an advice it would be appreciated

i was trying something like this but it is refusing to work. even by doing this i lost the month displaying and is now displaying a time left of 130k days .. 

$stmt = $con->prepare("SELECT  subscriptiondate, sub_time_remaining, sub_status FROM accounts WHERE id = ?");
$stmt->bind_param('i', $_SESSION['id']);
$stmt->bind_result($sub_date, $sub_time_rem, $sub_status);
 $stmt->execute();
 $stmt->fetch();
 $stmt->close();
$subscription_time = strtotime($sub_date);
$current_date = date("Y-m-d H:i:s");
$current_time = strtotime($current_date);
$diff2 = $current_time + $sub_time_rem;
$rem_time = date('d M Y H:i:s', $diff2);
$target = $rem_time;                   // SET OR GET TARGET TIME HERE
$targ = new DateTime($target);
$target_time = $targ->format('g:ia');
$target_date = $targ->format('F jS Y');
                 $remain = ['days' => 0, 'hrs' => 0, 'mins' => 0, 'secs' => 0];
                #$dt1 = new DateTime( $_GET['target'] );
                $dt2 = new DateTime('now');
                if ($targ > $dt2) {
                    $diff = $targ->diff($dt2);
                    $remain['days'] = $diff->days;
                    $remain['hrs'] =  $diff->h;
                    $remain['mins'] = $diff->i;
                    $remain['secs'] = $diff->s;
                  }

                  if ($diff2 == $diff2 - 1) {
$sub_time_rem2 = $diff2 - 1;
                    $stmt = $con->prepare('UPDATE accounts SET sub_time_remaining = ? WHERE id = ?');
                    $stmt->bind_param('ii', $sub_time_rem2, $_SESSION['id']);
                    $stmt->execute();
                    $stmt->close();
                  }

 

[kicken]

You shouldn't be trying to save a "time remaining" value at all.  That's a value you'd want to calculate on the fly when you need it based on the current time.

Either save the sub_date and the amount of time they are allowed, or save an expiration date for the subscription and calculate the difference between now and that expiration date to determine the time remaining.

 

[alexandre]

$stmt = $con->prepare("SELECT  subscriptiondate, sub_status FROM accounts WHERE id = ?");
$stmt->bind_param('i', $_SESSION['id']);
$stmt->bind_result($sub_date, $sub_status);
 $stmt->execute();
 $stmt->fetch();
 $stmt->close();
$subscription_time = strtotime($sub_date);
$current_date = date("Y-m-d H:i:s");
$current_time = strtotime($current_date);
$diff2 = $current_time + 2592000;
$rem_time = date('d M Y H:i:s', $diff2);
$target = $rem_time;                   // SET OR GET TARGET TIME HERE
$targ = new DateTime($target);
$target_time = $targ->format('g:ia');
$target_date = $targ->format('F jS Y');
                 $remain = ['days' => 0, 'hrs' => 0, 'mins' => 0, 'secs' => 0];
                #$dt1 = new DateTime( $_GET['target'] );
                $dt2 = new DateTime('now');
                if ($targ > $dt2) {
                    $diff = $targ->diff($dt2);
                    $remain['days'] = $diff->days;
                    $remain['hrs'] =  $diff->h;
                    $remain['mins'] = $diff->i;
                    $remain['secs'] = $diff->s;
                  }

?>

i changed it back like it was and now i just use the current time and calculate + a month in seconds which gives + 2592000, and if the current date minus the subscription date value is equal to 2592000 then they are unsbscribed but, right now calculating the month is working , simply the countdown isnt counting down anymore and i dont know why. i didnt touched to the countdown code except the target variable value..

Edited November 15, 2022 by alexandre

[alexandre]

got it the format was wrong for my target variable value .. my bad , but thank you for your help.