how-to-display-the-membership-expiration-date

[WebMedia_1]

Pls i need your help here, im working on web page in which i want the user who register or subscribe will login to know thier status either membership expired or active but this code it is working manually if i set the date but not working from the database and in my database i have a data table column called expired with timestramp

<?php
$now = time();
$sql="SELECT * FROM `user` WHERE (`username` ='$myusername' OR `lastname` ='$myusername') AND `password`='$mypassword';
    $expired=date("Y-m-d H:i:s", time());
    //echo $now_time."<br>";
    $today_date=strtotime($now_time);
    $d1_register=strtotime("2015-10-01 18:46:38");
    $mint=round(abs($today_date - $d1_register) / 60,2)." minute";
    $day=round(($mint/60)/24,2);

    if ($day>=30) {
        echo "Account Expired";
    } else {
        echo "Welcome Back!";
    }

    ?> 

[cereal]

Hi, you can do this in the select query, something like:

SELECT * FROM `user` WHERE `username` = ? AND `expires_at` < NOW()

The expression expires_at < NOW() will verify the date stamp with the current date. This requires that the column is datetime type, otherwise you have to cast. If expired, then it will return a result, otherwise it will return an empty result set.

Live example: http://sqlfiddle.com/#!9/40dfbc/3

[WebMedia_1]

Thanks @cereal, how to alert the user either his membership is expired or active with your code and what if renew his account

[cereal]

It depends: do you want to lock out the user from his profile or allow him to enter and display a message that invites him to renew the membership?

Above query is checking if user exists and if expired, but it will not work fine if typed user is wrong. To work fine this approch would require at least two queries:

• the first query to check if user exists, by searching the username and eventually the password (if you want to notify only the owner);
• the second query to check if expired based on user id got from the previous query.

To use only one query then, yes, you can use PHP, but I suggest DateTime as this is more simple:

# the $row variable carries the query result
$expires_at = new DateTime($row['expires_at']);
$today      = new DateTime('now');

if($expires_at < $today)
    echo 'Your account expired, please <a href="/renew.php">renew here.</a>';

else
{
    # enable session & redirect to profile
    $_SESSION['logged'] = true;
    $_SESSION['user_data'] = array('username', 'and', 'so', 'on');
    header('Location: http://site.tld/profile');
}

If it still does not work, please show an example of date returned by the database table.

[WebMedia_1]

thanks for your help @cereal but i dont want to be specific on single user as "john" but apply it to all usernames in the database how about using it as this, trying to replace username with john but not working

SELECT * FROM `user` WHERE `name` = 'username' AND `expires_at` < DATE_ADD(NOW(), INTERVAL 15 DAY);

instead of the following code

SELECT * FROM `user` WHERE `name` = 'john' AND `expires_at` < DATE_ADD(NOW(), INTERVAL 15 DAY);

thanks

[cereal]

To extend the query to all users just remove the name condition from the WHERE clause:

SELECT * FROM `user` WHERE `expires_at` < DATE_ADD(NOW(), INTERVAL 15 DAY);

and it should work fine.

[WebMedia_1]

thanks @cereal, it works