• 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

    • Yeah, it seemed like a bunch of 2D decal assets to me, so I didn't think it would be a huge issue to replace them. Assuming, of course, that Sony didn't just decide to settle and pay the artist instead. Do we know if that happened?
    • No worries, I wasn't pointing to your post specifically at all. I was talking in general and building upon your idea.
    • This 2TB NVMe Gen4 SSD is priced just $94 and you also get a free 64GB UHS-I memory card by Sayan Sen A few days ago, we reported on Team Group's T-FORCE G50 4TB NVMe SSD that was up for grabs at just $200 thanks to a promo coupon. Sadly, that deal has expired although you can still WD's SN8100 (Gen5) and SN7100 (Gen4) offerings as they are still running the discount. If you don't have the budget for those or are shopping for lower capacity drives then Crucial's T500 Gen4 drive discount is still live, and you can get them for just $125. And while the G50 4TB deal has expired, Team Group is now offering its 2TB model at its lowest ever price and you also get a free Micro SD card with it. The Team Group G50 is also a TLC (triple level cell) NAND flash SSD, and thus the endurance on the T-FORCE SSD is quite good, as it is rated for 1300 TBW (terabytes written) for the 2TB variant. Its MTBF, or Mean Time Between Failure, is claimed at 3,000,000 hours. The operating temperature is 0~70 C. The G50 does not have a dedicated DRAM cache (only the G50 Pro SKUs have it), but since it is based on NVMe version 1.4 which supports HMB (host memory buffer) technology; thus, the drive can use system memory for caching. In terms of performance, Team Group promises sequential read and write speeds of up to 5000 MB/s and 4500 MB/s, respectively. However, the firm does not disclose random throughput metrics. Get the Team Group G50 at the link below (deal is said to be ending in less than 10 hours): Team Group T-FORCE G50 SSD (TM8FFE002T0C129) + Team Group 64GB Elite microSDXC UHS-I U3, V30, A1, Micro SD with SD Adapter, to 100MB/s (TEAUSDX64GIV30A103): $105.99 + $12 off with promo code SSETA665 (Shipped and Sold by Newegg US) This Amazon deal is US-specific and not available in other regions unless specified. If you don't like it or want to look at more options, check out the Amazon US deals page here. Get Prime (SNAP), Prime Video, Audible Plus or Kindle / Music Unlimited. Free for 30 days. As an Amazon Associate, we earn from qualifying purchases.
    • I know what you're getting at, but Microsoft themselves have said that the Xbox Ally specifically disables a bunch of stuff from Windows to improve performance and save 2GB of memory. And that special game mode is coming to Windows 11 next year.
  • Recent Achievements

    • First Post
      Ian_ earned a badge
      First Post
    • Explorer
      JaviAl went up a rank
      Explorer
    • Reacting Well
      Cole Multipass earned a badge
      Reacting Well
    • Reacting Well
      JLP earned a badge
      Reacting Well
    • Week One Done
      Rhydderch earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      646
    2. 2
      ATLien_0
      269
    3. 3
      Michael Scrip
      218
    4. 4
      +FloatingFatMan
      184
    5. 5
      Steven P.
      146
  • Tell a friend

    Love Neowin? Tell a friend!