[SOLVED] Date function help

psychowolvesbane · May 26, 2008

Hi, I have a user list where I sort between various criteria from the database, one of which is to see which Inactive users haven't activated their account after 14 days, the date of registration is also a field in the table for comparison.

I have tried to select only those users who haven't activated after that time period but it just brings up all the inactive users instead of a few who registered more than 2 weeks ago.

Here is the code:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<?php
session_start();

if(isset($_POST['DelYes']))
{

}

?>

<html>
<head>
<title>Clothing Line</title>
<link href="stylesheetCL.css" rel="stylesheet">
<?php require('jscript.inc');

if(($_SESSION['User_Type'] == "Student") || ($_SESSION['User_Type'] == ""))
{
   header("Location:../index.php"); 
}
else
{
   $Action = $_GET['Action'];
   $NewAction = $_SESSION['NewAction'];

   if ($Action == "")
   {
      header("Location:admin.php");
   }

   $Sort = $_GET['Sort'];

   if($Sort =="")
   {
      $Sort = "All";
   }
}

?>
</head>
<body>
<div class="MainContainer">
<?php require('header.inc');
include "connect_details.php";
require('menu.inc');
?>

<div class="DivMain">
<?php

$conn = mysql_connect($Host,$Username,$Password) or die(mysql_error());
$db = mysql_select_db($Dbname, $conn);

if($Sort == "All")
{
   $Category = "All";
   $sql = "SELECT * FROM UserAccount";
}
elseif($Sort == "Admins")
{
   $Category = "Admins";
   $sql = "SELECT * FROM UserAccount WHERE AccountType='Admin'";
}
elseif($Sort == "Students")
{
   $Category = "Students";
   $sql = "SELECT * FROM UserAccount WHERE AccountType='Student'";
}
elseif($Sort == "Inactive")
{
   $Category = "Inactive";
   $sql = "SELECT * FROM UserAccount WHERE Status='0'";
}
elseif($Sort == "Unactivated")
{
   $Category = "Unactived";

   //THIS IS THE PROBLEM#########

   $sql = "SELECT * FROM UserAccount WHERE Status='0' AND DATE_SUB(NOW(), INTERVAL 14 DAY) >= DateOfReg ";

   //THIS IS THE PROBLEM#########
}

if ($Action == "Edit") 
{
   echo"<span class=\"head3\">List of Users - Category: ".$Category."</span><br><br>
   <a href=\"display_user_details.php?Action=Edit&Sort=All\">All Users</a> | <a href=\"display_user_details.php?Action=Edit&Sort=Admins\">Admins</a> | <a href=\"display_user_details.php?Action=Edit&Sort=Students\">Students</a> | <a href=\"display_user_details.php?Action=Edit&Sort=Inactive\">Inactive Users</a> | <a href=\"display_user_details.php?Action=Edit&Sort=Unactivated\">Overdue Activated Users</a><br><br>";

   
   $rs = mysql_query($sql, $conn);

   if(mysql_affected_rows($conn) == 0)
   { 
      echo"<span class='errmsg'>There are no users in this category</span><br><br>"; 
   }
   else
   {
      echo"<span class=\"head4\">Click on one to edit</span><br><br>  
      <table border=\"0\" cellpadding=\"3\"><tr>
        <th>Surname</th>
        <th>Email</th>
        <th>Campus</th>
        <th>Date Of Reg</th>
        <th>User Type</th>
        <th>Status</th>
      </tr>";

      while($row = mysql_fetch_array($rs)) 
      {
         $UserID = $row['UserID'];
         $Surname = $row['Surname'];
         $Email = $row['Email'];
         $Campus = $row['Campus'];
         $DateOfReg = $row['DateOfReg'];
         $UserType = $row['AccountType'];	
         $Status = $row['Status'];
         $Activity = "";

         if($Status == 0)
         {
            $Activity = "Inactive";
         }
         else
         {
            $Activity = "Active";
         }

         echo "<tr style=\"color:#E61732; line-height:8pt\"
         onMouseOver=\"this.style.backgroundColor='#FF7777';this.style.color='black';
         this.style.cursor='pointer'\"onMouseOut=\"this.style.backgroundColor='white';this.style.color='#E61732'\"
         onClick=\"location.href='edit_user_details_form.php?UserID=$UserID'\">
        
         <td class=\"tdcenter\">$Surname</td>
         <td class=\"tdcenter\">$Email</td>
         <td class=\"tdcenter\">$Campus</td>  
         <td class=\"tdcenter\">$DateOfReg</td>
         <td class=\"tdcenter\">$UserType</td>
         <td class=\"tdcenter\">$Activity</td>  
         </tr>";
      }
      mysql_close($conn);
      echo"</table>";
   }
}
elseif($Action == "Delete")
{
   echo"<span class=\"head3\">List of Users - Category: ".$Category."</span><br><br>";
   echo"<a href=\"display_user_details.php?Action=Delete&Sort=All\">All Users</a> | <a href=\"display_user_details.php?Action=Delete&Sort=Students\">Students</a> | <a href=\"display_user_details.php?Action=Delete&Sort=Admins\">Admins</a> | <a href=\"display_user_details.php?Action=Delete&Sort=Inactive\">Inactive Users</a> | <a href=\"display_user_details.php?Action=Delete&Sort=Unactivated\">Overdue Activated Users</a><br><br>";

   $rs = mysql_query($sql, $conn);

   if(mysql_affected_rows($conn) == 0)
   { 
      echo"<span class=\"errmsg\">There are users in this category</span><br><br>"; 
   }
   else
   {  
      echo"<span class=\"head4\">Click on one to delete</span><br><br>
      <table border=\"0\" cellpadding=\"3\"><tr>
        <th>Surname</th>
        <th>Email</th>
        <th>Campus</th>
        <th>Date Of Reg</th>
        <th>User Type</th>
        <th>Status</th>
      </tr>";

      while($row = mysql_fetch_array($rs)) 
      {
         $UserID = $row['UserID'];
         $Surname = $row['Surname']; 
         $Email = $row['Email'];
         $Campus = $row['Campus'];
         $DateOfReg = $row['DateOfReg'];
         $UserType = $row['AccountType'];	
         $Status = $row['Status'];
         $Activity = "";

         if($Status == 0)
         {
            $Activity = "Inactive";
         }
         else
         {
            $Activity = "Active";
         }

         echo "<tr style=\"color:#E61732; line-height:8pt\"
         onMouseOver=\"this.style.backgroundColor='#FF7777';this.style.color='black';
         this.style.cursor='pointer'\"onMouseOut=\"this.style.backgroundColor='white';this.style.color='#E61732'\"
         onClick=\"if(confirm('Are you sure you wish to delete this user?'))window.location.href='delete_user.php?UserID=$UserID'\">
        
         <td class=\"tdcenter\">$Surname</td>
         <td class=\"tdcenter\">$Email</td>
         <td class=\"tdcenter\">$Campus</td> 
         <td class=\"tdcenter\">$DateOfReg</td> 
         <td class=\"tdcenter\">$UserType</td>
         <td class=\"tdcenter\">$Activity</td>  
         </tr>";
      } 
      mysql_close($conn);
      echo"</table>";
   }

}

if($Sort == "Unactivated")
{
   echo"
   <br><span class=\"head4\">All of the above users have not activated their account in the alloted 2 weeks, do you wish to remove them?</span>
   <form method=\"post\" name=\"DeleteUnactivated\" action=\"display_user_details.php?Action=$Action\">
   <input type=\"submit\" name=\"DelYes\" class=\"buttonS\" style=\"overflow:visible; width:auto\" value=\"Yes\" onMouseOver=\"OverMouse(this)\";  onMouseOut=\"OutMouse(this)\"/>";
}

if($NewAction == "Added")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User successfully Added!</span></div>";    
}
elseif($NewAction == "NotAdded")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User could not be Added!</span></div>"; 
}

if($NewAction == "Edited")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User successfully Edited!</span></div>"; 
}
elseif($NewAction == "NotEdited")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User could not be Edited!</span></div>"; 
}

if($NewAction == "Deleted")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User successfully Deleted!</span></div>"; 
}
elseif($NewAction == "NotDeleted")
{
   echo"<br><div style='position:relative; top:100x; left:0px; width:300px'>
   <span class='head4'>User could not be Deleted!</span></div>"; 
}

echo"<br><br><a href=\"admin.php\">Back to Admin Menu</a>";
unset($_SESSION['NewAction']);
?>
</div>
</div>
</body>
</html>

Barand · May 26, 2008

How is your "dateOfReg" column defined/formatted?

psychowolvesbane · May 26, 2008

Yeah, sorry it's 2008-09-27 (Y-m-d).

psychowolvesbane · May 26, 2008

Edit: Okay I did a little bit of searching for the SQL Date_Sub() function and came across a solution:

$sql = "SELECT * FROM UserAccount WHERE Status='0' AND DateOfReg <= {fn DATE_SUB({fn CURDATE()}, INTERVAL 14 DAY)}";

Thanks anyway for trying!

Sign In

[SOLVED] Date function help

Recommended Posts

psychowolvesbane

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

psychowolvesbane

Link to comment

Share on other sites

psychowolvesbane

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information