PHP & MYSQL: Update data in the database after a period of time

[seb86]

I would like to update data in the database after a period of time has gone by.

For example, I have an item i wish to auction on my site for 7 days. After 7 days has gone, the item does not show on the site.

But I still want the data to be in the database, so the function should allow me to update the data on any value within the database table that I choose. This way if I decide to put the item back on again for the same period or longer, I can do so.

If any one out there can help me start this function I would be ever so greatful. I can't seem to find any tutorials that will help me on this.

The only code that I have that will let me select how many days I want the item on my site is this:

<?php
for($i=1; $i<=365; $i++) 
if($max_time==$i) print "<option value='$i' selected>$i Days</option>";
else print "<option value='$i'>$i Days</option>";
?>

I still need a function that will tell the website that this item can no longer be displayed.

If any one can help me on this, I would appreciate very much.

I will look forward in your replies.

Thank you.

[C++]

To achieve this you don't need code that updates the database periodically. That is cumbersome and inefficient. All you need is to add an expiry time field to your auctions in the database, and then make sure your website only displays auctions that are not expired. Unfortunately teaching you the full semantics of this would take more time than I have, but hopefully this points you in the right direction. Here are some references of interest as well.

http://dev.mysql.com/doc/refman/5.1/en/dat...-functions.html

http://us3.php.net/manual/en/ref.datetime.php

[seb86]

Ok i have got the first stage complete where i can set the amount of seconds for how long the item stays on the website.

Now i need to get the last function working that checks to see what items should expire.

Here is the code that I have written so far.

$qCheck = "SELECT auto_remove FROM ".TBL_SITE_SETTINGS." WHERE auto_remove='$AUTOremove'";
$result = mysql_query($qCheck) or die("ERROR: ". mysql_error());
if($AUTOremove == "yes")
{
	$timenow = time(); // The time and date it is now in form of seconds.
	$datenow = date("Y:m:d H:i:s"); // The date it is now in human terms.
	$qCollect = "SELECT * FROM ".TBL_ITEMS." WHERE date_published<$datenow AND status='1' AND approved='1' AND active='1' AND expire_time<$timenow";
	$result = mysql_query($qCollect) or die("SELECT ERROR: ". mysql_error());
	while($expire = mysql_fetch_array($result)){
		$dateadvertised = $expire['date_published'];
		$expired = $expire['expire_time'];
	}
	if(is_null($expired)){ /* Do Nothing! */ }
	elseif($expired == '0'){ /* Do Nothing! */ }
	else{
		if($dateadvertised == '<$datenow'){
			//$qExpire = "UPDATE ".TBL_ITEMS." SET expire_time<$timenow, active='0' WHERE date_published='$dateadvertised<$datenow' AND status='1' AND approved='1' AND active='1'";
			$qExpire = "UPDATE ".TBL_ITEMS." SET expire_time='<$timenow', active='0' WHERE date_published='$dateadvertised<$datenow'";
			$update = mysql_query($qExpire) or die("UPDATE ERROR: ". mysql_error());
		}
	}
}

I have 2 values that I am struggerling a little to get working for what I need it to do. The first value is the date of the item was put on the site. The second is the time the item can be on the site for. The first function is easy and works fine. It checks if the site has been set to remove all items that have expired automatically. If it equalls yes then the rest of my code will act. Now what I am trying to achieve is for the code to check the date the items where put on the site against how long the item can be on the site before it expires. This is done by checking the date the item was added against the date is now and then calculate how long the item has left before expiring. If the item has expired then the item will be updated where it removes the item from the site and is no longer visible to the public.

Now for the problems

Where it reads:

$qCollect = "SELECT * FROM ".TBL_ITEMS." WHERE date_published<$datenow AND status='1' AND approved='1' AND active='1' AND expire_time<$timenow";
	$result = mysql_query($qCollect) or die("SELECT ERROR: ". mysql_error());

I get the following error:

SELECT ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':06:19 06:12:13 AND status='1' AND approved='1' AND active='1' AND expire_time<1' at line 1

I'm assuming it's because of the date_published part. I need it to be in datetime format in the SQL. I need it to select all items that have a date and time that is older than the current date and time.

If i can get this fixed first then I can test the rest of the code to see if it works. If you do see something else that is wrong with the code and you are able to fix that also i would be ever so greatful.

Thank you.

[AnthonySterling]

Your using a colon to delimit the date, this is unacceptable. Your date should be in the following format:-

2007-12-31 23:59:59

[seb86]

I can't believe I didn't spot that error. I have corrected it but it is still not working. I now get this:

SELECT ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16:10:26 AND status='1' AND approved='1' AND active='1' AND expire_time<12454494' at line 1

It's now not accepting the H:i:s . What do i do now?

[AnthonySterling]

Save yourself the trouble and use FROM_UNIXTIME in your query to convert the timestamp to MySQL date format.

SELECT * FROM ".TBL_ITEMS." WHERE date_published < FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time < $timenow

:)

[seb86]

Save yourself the trouble and use FROM_UNIXTIME in your query to convert the timestamp to MySQL date format.

SELECT * FROM ".TBL_ITEMS." WHERE date_published < FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time < $timenow

:)

Ok that helped, thank you.

Now am allowed to go to the next part of the code to see if it works. My security checks for NULL work but, I now need to correct the update function for the item to expire correctly. At the moment it will only expire the item if it is older than the current date and not the time with it. It ignores how many seconds there are for the item to expire and only expires the item if it is a day old or more. It does not count how long the item can be on for. This is set by the expire_time field in the database.

I have placed a copy of the code that I have written so far. There are a few modifications that I have made since. I hope you can help me with the rest as this is the first time I have delt with time in PHP. This is the only part I need to get done and it's complete.

/* Checks for any items that need to be set to expire if any */
$qCheck = "SELECT auto_remove FROM ".TBL_SITE_SETTINGS." WHERE auto_remove='$AUTOremove'";
$result = mysql_query($qCheck) or die("ERROR: ". mysql_error());
if($AUTOremove == "yes")
{
	$timenow = time(); // The time and date it is now in form of seconds.
	$datenow = date("Y-m-d H:i:s"); // The date it is now in human terms.
	$qCollect = "SELECT * FROM ".TBL_ITEMS." WHERE date_published<FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time<$timenow";
	$result = mysql_query($qCollect) or die("SELECT ERROR: ". mysql_error());
	while($expire = mysql_fetch_array($result)){
		$dateadvertised = $expire['date_published'];
		$expired = $expire['expire_time'];
	}
	if(is_null($expired)){ /* Do Nothing! */ }
	elseif($expired == '0'){ /* Do Nothing! */ }
	elseif($dateadvertised == '$empty'){ /* Do Nothing */ }
	else{
		//From here I am trying to get it correct and working. I have 3 different versions of $timeleft but I don't know which one will work. //
		//$timeleft = $dateadvertised<FROM_UNIXTIME($timenow);
		//$timeleft = $dateadvertised<$datenow;
		$timeleft = $dateadvertised<$timenow;
		$qExpire = "UPDATE ".TBL_ITEMS." SET active='0', expire_time='<$timenow', expired='yes' WHERE date_published='$timeleft' AND status='1' AND approved='1' AND active='1' AND expired='no'";
		$update = mysql_query($qExpire) or die("UPDATE ERROR: ". mysql_error());
	}
}

I will say what I need again. For example, I have an item i wish to auction on my site for 7 days. After 7 days has gone, the item does not show on the site. It doesn't matter if it is 7 days or not the point is that I need it to expire no matter how long the item can stay on for. So that means if I put in the date_published field 2009-06-20 12:50:00 and I put 604800 seconds in the expire_time field which is 7 days, the code should then count how many seconds are left before it updates the item to expire and not just expire because 7 days have gone. It also has to count the hours, minutes and seconds from the time the item was put on the site.

I hope that makes sense.

Please ask me any questions if you need to about how the function goes.

Thanks again for the help so far.

[seb86]

I think i got it working now. Thank you for the help you gave me.