php mysql帐户订阅到期

Question

I have a mysql database of users which has a subscription expiry field (sub_expire) which is timestamp format at present and also another field (active) which is enum yes or no which is yes allows login and if no refuses login.

How do I using php check if the subscription expiry date has either been reached or has passed upon a user attempting to login and then update the active field to no so that the user is unable to login.

Many thanks in advance

Answer 1

This gets if the users by $username and checks if sub_expire smaller than the current time/date. if yes active get set to no
As mentioned by @AgeDeO you should not save active and sub_expire as both have the same usage

$SQL_CONN = mysqli ("host",
                    "user",
                    "password",
                    "dbname",
                    "port");

$sqlStm = $SQL_CONN->prepare("select u.user_id, u.sub_expire < NOW from users u where u.name = ?;");
$sqlStm->bind_param ("s", $username);

$sqlStm->execute();

$sqlStm->store_result();

$sqlStm->bind_result($id, $active);

$sqlStm->free_result();
$sqlStm->close();

if(!$active) {

  $sqlStm = $SQL_CONN->prepare("update users set active =`no` WHERE user_id = ?");
  $sqlStm->bind_param ("i", $user_id);

  $sqlStm->execute();
  $sqlStm->free_result();
  $sqlStm->close();
}