Jump to content

[SOLVED] Date function help

psychowolvesbane

By psychowolvesbane
in PHP Coding Help

 Share

Recommended Posts

psychowolvesbane Member

psychowolvesbane

Posted
Posted

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>

Link to comment
https://forums.phpfreaks.com/topic/107362-solved-date-function-help/
Share on other sites
psychowolvesbane Member

psychowolvesbane

Posted
  • Author
Posted

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!

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.