• 0

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


Question

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.

7 answers to this question

Recommended Posts

  • 0

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

  • 0

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.

  • 0

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?

  • 0

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 &lt; FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time &lt; $timenow

:)

  • 0
  SilverBulletUK said:
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 &lt; FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time &lt; $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&lt;FROM_UNIXTIME($timenow) AND status='1' AND approved='1' AND active='1' AND expire_time&lt;$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&lt;FROM_UNIXTIME($timenow);
		//$timeleft = $dateadvertised&lt;$datenow;
		$timeleft = $dateadvertised&lt;$timenow;
		$qExpire = "UPDATE ".TBL_ITEMS." SET active='0', expire_time='&lt;$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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Burrrrn. Bought COD WWII last month when it was on sale for PC. Oh well. Excellent game tho, and the PC version plays/looks amazing (and has it's own PC achievements). ...wondering if this is a lesson on waiting on sales. ...also hoping Rise of the Tomb Raider has it's own PC Achievements
    • Google Chrome 137.0.7151.120 (offline installer) by Razvan Serea The web browser is arguably the most important piece of software on your computer. You spend much of your time online inside a browser: when you search, chat, email, shop, bank, read the news, and watch videos online, you often do all this using a browser. Google Chrome is a browser that combines a minimal design with sophisticated technology to make the web faster, safer, and easier. Use one box for everything--type in the address bar and get suggestions for both search and Web pages. Thumbnails of your top sites let you access your favorite pages instantly with lightning speed from any new tab. Desktop shortcuts allow you to launch your favorite Web apps straight from your desktop. Chrome has many useful features built in, including automatic full-page translation and access to thousands of apps, extensions, and themes from the Chrome Web Store. Google Chrome is one of the best solutions for Internet browsing giving you high level of security, speed and great features. Important to know! The offline installer links do not include the automatic update feature. Google Chrome 137.0.7151.120 changelog: [$7000][420697404] High CVE-2025-6191: Integer overflow in V8. Reported by Shaheen Fazim on 2025-05-27 [$4000][421471016] High CVE-2025-6192: Use after free in Profiler. Reported by Chaoyuan Peng (@ret2happy) on 2025-05-31 [425443272] Various fixes from internal audits, fuzzing and other initiatives Download web installer: Google Chrome Web 32-bit | Google Chrome 64-bit | Freeware Download: Google Chrome Offline Installer 64-bit | 128.0 MB Download: Google Chrome Offline Installer 32-bit | 115.0 MB Download page: Google Chrome Portable Download: Google Chrome MSI Installers for Windows (automatic update) View: Chrome Website | Release Notes Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • -Drop the art style, it's cool but doesn't fit the franchise at all. -Make it a gritty single player game, like Halo -Include deathmatch and all that stuff, extraction can be a separate mode If they don't do that, it's dead on arrival IMO.
    • It's a shame because it's one of the best immersive sims ever made, even though it could have used a bit more monster variety IMO.
  • Recent Achievements

    • One Month Later
      Custom Greek Shirts earned a badge
      One Month Later
    • Week One Done
      Custom Greek Shirts earned a badge
      Week One Done
    • One Year In
      Custom Greek Shirts earned a badge
      One Year In
    • Week One Done
      topantidetectbrowser earned a badge
      Week One Done
    • Explorer
      Jdoe25 went up a rank
      Explorer
  • Popular Contributors

    1. 1
      +primortal
      672
    2. 2
      ATLien_0
      281
    3. 3
      Michael Scrip
      223
    4. 4
      +FloatingFatMan
      190
    5. 5
      Steven P.
      146
  • Tell a friend

    Love Neowin? Tell a friend!