I've done something similar (checking if a list of articles are more than 2 weeks old and moving those affected to another table or listed as 'old').however,i did this in access/asp.but the technique should apply as well.
See if mysql has a function using 'time' to carry out specific actions on a table based on a criteria.i did this in access, copied the code and added it as a function in dreamweaver.i'm hoping mysql has the same.hope this helps.i'm using my phone to post this,if i have the time i'll post a link.cheers!

tunde,

I haven't implemented this before (but who knows, I might eventually so I might as well figure out how to now )) but this link may have all the answers you seek: http://www.thescripts.com/forum/thread614886.html

It mentions the mysql event scheduler.

I hope this helps.

This is how I would approach the problem, I am bored

Note: This is an illustration:

Tables
id
User
Date
Duration
Status (Active,Pending,Expired)

Program [CRON]
SELECT date from table where id=''

$date = substr($todays_date, 6, 2);

$trigger_date = date("Ymd", mktime (0,0,0,$month,$date-$number_of_days,$year));

You just needed the calculation right?

use cookies

setcookie("Example", "User", time()+3600); expires in 1 hour

read more at http://us.php.net/setcookie

You will also need a field in you user table that tracks the timestamp when the person login.

so, I have been thinking,

In the interest of time (at least for a first pass), why not bypass the CRON altogether? You could do this on the fly in your code:

Say you have the 2 timestamp fields. You do a select * on the mySQL table, then get the 2 timestamps fields. Then either do the calculation using a mySQL function (if it exists. I don't know as I have had no reason to research this) or in your PHP, then run your calculation. If the value exceeds whatever number you set, either you don't display that record or you display it with a "disabled" text or whatever in a "status" column of your HTML table.

Probably not too pretty, but that could buy you time while you figure out your CRON job thingie

Make sense?

lol my illustration should be able to solve your issue if you are a programmer? You require the entire code is it? I can write it for you, but busy at the moment. If you can wait till sunday.

My bad, it was late and I wasn't thnking straight. Here is a simple approach

I would create the table like this(add more fields if you like)

[Table]
id
Property
User
StartDate
EndDate
Duration
Active

Have the CRON file run daily, so it calculates the remaining days and deactivates any listing that has a zero duration
[CRON]
Update table set duration= (EndDate - StartDate)
Update table set Active = 0 where duration = 0

I got bored and quickly wrote a simple code to do what you want, modify to suit your needs.

Note: I didn't debug it. So if you have any bug, kindly let me know

id => user id
sdate => the date the counting should begin
days => number of days to expire
status => Client Account status

<?php 


$resultx = mysql_query("SELECT * FROM client WHERE status='Active'" 
or die(mysql_error());  

 $x = 0;
      while ($datax = mysql_fetch_array ($resultx))
      {
        $id = $datax['id'];


$days_to_expire = $datax[days];

//should be in the format of "Ymd"
$sdate = $datax[startdate] ;

//todays date
$todays_date = date('Ymd', mktime (0, 0, 0, date ('m'), date ('d'), date ('Y')));

$timeStamp = strtotime("$sdate";
$timeStamp += 24 * 60 * 60 * $days_to_expire ; // (add expirydate days)
$expiry_date = date("Ymd", $timeStamp);

 //select information from client where client is due for expiry
      if ($expiry_date == $todays_date){
   $query = '' . 'UPDATE client SET status=\'Expired\' WHERE id=\'' . $id . '\'';
        $result2 = mysql_query ($query);
      }

 
  
      ++$x;
      }
      

      $report = '' . $x . ' Number of Account Expired';
?>

*my2cents correction applied.

maaan kazey, see groove! You beat me to it

In any event, I think there might be a very minute bug on line 1 - SELECT * FROM client WHERE status='Active'

thot cron was used to run files

when did it start running queries

@webguru

It is used to run files. Those were just examples of what you will place in the file that cron will run.

ok i thot it was somethin new

im cool

I have a similar coding issue,  However, It seems far less complicated,  I have a recordset that returns the startdate and enddate. I want to check the startdate against the end date. If the users enddate has passed, redirect them to an "expired.php" page, if not, continue; in that same function if the startdate is after the current date redirect them to a "notyet.php" page that displays their startdate,

The expired page works, which is strange the notyet page doesn't,

Here's what I have so far,

<?php
	$startdate = $row_product['startdate'];
	$enddate = $row_product['enddate']; 
	$todays_date = date("Y-m-d";
	
    if ($enddate <= $todays_date) { 
		header('Location: expired.php');
	}
	elseif ($startdate < $todays_date){
		header('Location: notyet.php');
	}else {
	} 
?>

Any help would be a better than perfect!

I think you are missing a function to be wrapped around your dates: strtotime()

So it would be something like this (pseudocode):
start date = strtotime(start date);
end date = strtotime(end date);
today = strtotime(today);

Then you do your comparisons (again, pseudocoe):
if (end date <= today) then
    do stuff
else if (start date < today) then
    do stuff
else
    do stuff

Note:
1) You can't compare dates directly in "Y-m-d" format (or maybe you can but I just don't know  ) because of the "-".  At the very least, you would need to apply "explode".  Having said that, I think my approach works better and is more straightforward.  The strtotime() will convert to a unix timestamp which looks like an actual number that makes checking easier

2) you will still need the $todays_date = date("Y-m-d";  part above.  of course the assumption is the dates you are getting from ur dates columns r in the same format. if not, you will need to convert them such that strtotime() can take and convert them.

Finally, thanks to actually searching the archives for this instead of writing a new post. Reading this from top to bottom actually made it easier for me to follow and appreciate.

Good luck!

Hello,

Well this is my first post so i hope this is helpful. I noticed the $x variable is being incremented
by one regardless of if it is a expired add or not. This is fixable by removing the ++$x; and putitng
it in the if() statement like below:

<?php
//select information from client where client is due for expiry
if ($expiry_date == $todays_date){
    $query = '' . 'UPDATE listings SET status=\'Expired\' WHERE id=\'' . $id . '\'';
     $result2 = mysql_query ($query);
     $x++;
}
?>

I added a field to my database called numberdays to set the expire time to a different
one depending on the add. Here is my finished code in case it helps anyone.

<?php
// Lets check to see if any featured ads are expired
$resultx = mysql_query("SELECT * FROM listings WHERE status='Active'";
$x = 0;
while ($datax = mysql_fetch_array ($resultx)) {
	$id = $datax['id'];
	$days_to_expire = $datax['numberdays'];
	
	//should be in the format of "Ymd"
	$sdate = $datax['datestarted'] ;
	
	//todays date
	$todays_date = date('Ymd', mktime (0, 0, 0, date ('m'), date ('d'), date ('Y')));
	
	$timeStamp = strtotime("$sdate";
	$timeStamp += 24 * 60 * 60 * $days_to_expire ; // (add expirydate days)
	$expiry_date = date("Ymd", $timeStamp);

	//select information from client where client is due for expiry
	if ($expiry_date == $todays_date){
   		$query = '' . 'UPDATE listings SET status=\'Expired\' WHERE id=\'' . $id . '\'';
        $result2 = mysql_query ($query);
        $x++;
 	}
}
$report = '' . $x . ' Number of Account Expired';
?>

Here is my table for this code:

CREATE TABLE IF NOT EXISTS `listings` (
  `id` bigint(20) NOT NULL auto_increment,
  `vid` bigint(20) NOT NULL,
  `sectionid` varchar(20) NOT NULL,
  `datestarted` varchar( NOT NULL,
  `numberdays` tinyint(4) NOT NULL,
  `status` varchar(25) NOT NULL,
  PRIMARY KEY  (`id`)
);


I keep my listings in a different table but you get the idea. This way I can have ads
that list for 30,45,90 days if I want.

Hope this helps.

Regards,
Ray

The PHP keyword "die" - what exactly does it do and what other substitutes can be used in its place?

die() ends the execution of a script.

die() - php
end - visual basic
stop - qbasic
All of them stops the execution of the program . . . dont confuse die with exit in php. . . just look 'em up in the php manual