Member account management - removing access when membership expires (PHP/MySQL)

The most efficient way of approaching this problem is to check the expiry date at the same time as checking the username and password. By doing so, you can redirect the user immediately to another page. The following recipe involves editing the Dreamweaver Log In User server behavior code. Once you edit the code, the server behavior will no longer be recognized by Dreamweaver.

This recipe assumes you are familiar with creating a login system using the Log In User server behavior.

Apply the Log In User server behavior

Create a login form with text input fields for username and password, and then apply the Dreamweaver Log In User server behavior by clicking the plus button in the Server Behavior panel. Select User Authentication > Log In User,  fill in the dialog box, and click OK.

Open Code view and locate the following section of code:

$LoginRS__query=sprintf("SELECT username, password 
                           FROM users WHERE username=%s AND password=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));
   
  $LoginRS = mysql_query($LoginRS__query, $cs5read) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     $loginStrGroup = "";
    

Assuming that the expiry date is stored in a table column called expiry, amend the code like this:

$LoginRS__query=sprintf("SELECT username, password, UNIX_TIMESTAMP(expiry) AS expiry 
                           FROM users WHERE username=%s AND password=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));
   
  $LoginRS = mysql_query($LoginRS__query, $cs5read) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     // get the current date
     $now = date();
     // compare the expiry date with the current date
     // if it's earlier, redirect the user to another page and exit
     if ($LoginRS['expiry'] < $now) {
        header('Location: expired.php');
        exit;
     }
     $loginStrGroup = "";

This amends the SQL query to get the expiry date. It uses the MySQL UNIX_TIMESTAMP() function to convert the date from MySQL format to a timestamp that can be compared with a PHP timestamp, and creates an alias using the AS keyword to reassign the result to expiry.

The extra code after if ($loginFoundUser) { gets a PHP timestamp for the current date, and compares it with the expiry date from the SQL query. If the value is lower, the header() function redirects the user to expired.php, and then exits the script. If the expiry date is greater than the current date, this code is ignored, and the user is logged in as usual.

Change the column names and the name of the redirect page to match your setup.

When you make this changes, Dreamweaver will warn you that it can't recognize the Log In User server behavior. Just click OK to dismiss the warning.