• 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

    • Amazon's Lab126 ventures into "Physical AI" with new robotics team by Paul Hill Amazon has announced that it’s forming a new agentic AI team within its secretive hard research and development division, Lab126, to begin work on physical AI. Specifically, the company is looking to develop an agentic AI framework for use in robotics, which could start to impact blue-collar jobs, especially at its warehouses. Agentic AI is one of the latest developments in AI, superseding the previous generative AI that took off with the launch of ChatGPT. Agentic AI models are special because they can complete multi-step actions for the user to complete complex tasks. Thanks to all the visual and audio capabilities added to generative AI in previous years, these agentic models can perceive their environment, reason, plan, and act to achieve goals with minimal human intervention. If Amazon can successfully bring agentic AI to robots, they will finally be able to interact with the real world in a way they can’t today, as software running on a computer. Many people are concerned about AI’s impact on white-collar jobs right now, but when Amazon develops physical AI, it will also affect blue-collar manual work. The work is going to be carried out by Amazon’s R&D company, Lab126. It was set up over 20 years ago and has created many iconic Amazon devices, including the Kindle, Fire tablets, Amazon Fire TV, Amazon Echo devices, and more. Who it affects, and how The biggest impact of physical AI developed by Lab126 will be on Amazon’s warehouses and logistics. The company said it wants to create robots that can perform tasks based on natural language instructions. As usual for a big tech company, Amazon claims that these robots will be assistants, but it’s difficult to see how they won’t reduce the need for people. Solely based on Amazon’s plans to automate work in its factories, customers will see an indirect impact from the move through faster deliveries and potentially lower costs. The decision by Amazon to focus on agentic AI in robots is pretty interesting because so far, we’ve mainly been hearing about agentic AI limited to computer applications, such as intelligent web browsers like Opera Neon. Why it's happening Amazon has a reputation for being an efficient company, particularly when it comes to the employment of warehouse workers who are known to have strict restroom breaks. Creating robots that can help speed up warehouse activities will further boost efficiency at the company and could potentially reduce its costs and improve safety. The beginning of work on physical AI is just the next evolution of AI that we could start to hear about in the coming months and years. As agentic AI gets better, companies will be looking to see what they can advance next and physical AI may be where they choose to go next; it certainly seems like this is what Amazon has settled on in this move. If Amazon’s physical AI doesn’t lead to mass layoffs of warehouse employees, it could drastically boost worker safety. Employees could potentially be less fatigued from moving around so much, which could lead to better concentration and fewer accidents. Right now, Amazon claims that these robots will only be assistants and not replacements. While Amazon will certainly be a leader in physical AI, given its massive wealth to throw at the problem, once the technology is available, it will likely be available for sale to other businesses to use, too. Caveats and what to watch for While it’s a notable development, it still sounds like Amazon is in the early stages of developing these physical AI systems, given that it has only just set up the team. We also don’t know what specific products Amazon is planning to build or the timelines for deployment. Ever since generative AI came onto the scene, there has been discussion of AI safety. With AI moving into the physical world, it will also bring up discussion about the safety concerns. Current measures are mainly concerned with AI software running on computers, not when it interacts physically with the world. Finally, and probably the biggest concern, what will these “assistants” do to people’s jobs? Companies will likely find themselves bringing in fewer new hires initially, but it could also displace people from their jobs. Source: CNBC
    • Nintendo Switch 2 launches, where to buy and a list of games that it may not support by Sayan Sen Nintendo announced the Switch 2 back in early April this year and then followed that up with more details related to performance and hardware features later. The company touted 10x the performance of the Switch. However, on the flip side, the battery suffers, and you also need new microSD Express cards for storage. For those who need a refresher, here are the technical specification details of the Switch 2: Specification Details Dimensions Approx. 166mm x 272mm x 13.9mm (with Joy-Con 2 attached); Maximum thickness from control stick tip to ZL/ZR buttons: 30.7mm Weight Approx. 401g (console only); Approx. 534g (with Joy-Con 2 controllers attached) Screen 7.9-inch capacitive touch LCD; 1920x1080 resolution; HDR10 support; VRR up to 120 Hz CPU/GPU Custom processor made by NVIDIA Storage 256 GB UFS (a portion reserved for system use) Communication Wireless LAN (Wi‑Fi 6), Bluetooth; Wired LAN available in TV mode via dock Video Output Up to 3840x2160 at 60 fps via HDMI in TV mode; Supports 120 fps at lower resolutions; HDR10 enabled Audio Output Linear PCM 5.1 channel via HDMI; Stereo speakers Microphone Built-in monaural microphone with noise cancellation, echo cancellation and auto gain control Buttons POWER and Volume buttons USB Ports 2 USB Type-C ports (bottom port for charging/dock connection; top port for accessories/charging) Audio Jack 3.5mm stereo mini plug (CTIA standard) Game Card Slot Supports both Nintendo Switch 2 and Nintendo Switch game cards Expansion Slot microSD Express card slot (compatible with cards up to 2 TB; other microSD cards can copy screenshots and videos) Sensors Accelerometer, gyroscope, brightness sensor Battery Lithium-ion, 5220 mAh; Approx. 2–6.5 hours lifetime; 3-hour charge time in sleep mode Dock Approx. 115mm x 201mm x 51.2mm; Weight: approx. 383g For those looking to get one, major retailers like Walmart, GameStop, Best Buy, and Target have all confirmed that they will have limited console stock from time to time so you will need to be on alert and check back. Nintendo has also published a full list of games that may not work on the Switch 2: Borderlands 3 Chrono Cross: The Radical Dreamers Edition Crash Bandicoot N-Sane Trilogy Guilty Gear XX Accent Core Plus R KarmaZoo Marvel vs. Capcom Fighting Collection: Arcade Classics Mortal Kombat 1 Overwatch 2 Star Wars: Knights of the Old Republic II: The Sith Lords Star Wars Republic Commando Super Mega Baseball 4 Tombi! Special Edition Tony Hawk's Pro Skater 1+2 Touhou Genso Wanderer Reloaded Ty the Tasmanian Tiger HD Warriors: Abyss However, keep in mind that Nintendo last updated the support list last month on May 27th and the company may still be testing these. So keep an eye on the official list of games on this webpage here on Nintendo's site. Have you managed to pick up the Nintendo Switch 2? Let us know in the comments.
    • Court orders Apple to keep web links in the App Store, eroding its iOS payment monopoly by Fiza Ali Apple has been ordered to continue permitting web links and external payment options in the App Store after its bid to halt court’s ruling was declined today by a higher court. Earlier this year, in April, a federal judge decreed that Apple must allow developers to include web links in their iOS apps, remove restrictions on link formatting, and enable external payment methods without taking a commission on transactions. Apple immediately appealed and sought an injunction to delay implementation of the order while the case progressed. However, the United States Court of Appeals has now refused Apple’s emergency request to stay the district court’s order. In its decision, the panel held that Apple had not demonstrated a sufficient likelihood of success on appeal, nor that it would suffer irreparable harm if the order were enforced. The court also considered potential prejudice to other parties and the public interest, concluding that an immediate suspension was not warranted. This ruling makes it much harder for Apple to overturn the April decision, which came from a lawsuit initiated by Epic Games. Epic first sued Apple’s App Store policies in 2020, claiming that the company’s restrictions harmed competition. While Epic did not prevail on every count, the court did rule that Apple must allow developers to inform users of alternative purchasing options at better prices. Despite that narrow victory, Apple repeatedly failed to conform to the terms from the original 2021 ruling, prompting the judge in April to issue a more detailed order outlining precisely how the App Store must be “opened up”. In response to the April ruling, prominent third-party apps have swiftly implemented web-based purchasing links. Both Spotify and Amazon’s Kindle app now include buttons directing users to purchase subscriptions via their websites, bypassing Apple’s in-app payments. Additionally, Fortnite has made a comeback on iOS after around five years, presenting users with the choice between Apple’s in-app payment system and Epic’s own payment and rewards mechanism. According to Epic CEO Tim Sweeney, there is presently a 60:40 split in usage favouring Apple’s system over Epic’s, though the gap appears to be narrowing. An Apple spokesperson, Olivia Dalton, issued a statement expressing the company’s disappointment: For now, Apple must comply with the existing injunction. Unless the Appeals Court later overturns the ruling, developers can continue to include web payment links, and Apple’s longstanding monopoly over iOS payment processing may continue to erode. The ultimate resolution will depend on the outcome of the ongoing appeals, which could set a significant precedent for how app marketplaces operate in the future. Source: The Verge
    • Reddit posts are all public, no login(therefore no agreeing to contract) to view the content. It's like the equivalent of sitting in a library and writing down notes from a textbook without signing it out and they start suing you for writing notes.
  • Recent Achievements

    • Rookie
      GTRoberts went up a rank
      Rookie
    • First Post
      James courage Tabla earned a badge
      First Post
    • Reacting Well
      James courage Tabla earned a badge
      Reacting Well
    • Apprentice
      DarkShrunken went up a rank
      Apprentice
    • Dedicated
      CHUNWEI earned a badge
      Dedicated
  • Popular Contributors

    1. 1
      +primortal
      397
    2. 2
      +FloatingFatMan
      177
    3. 3
      snowy owl
      170
    4. 4
      ATLien_0
      167
    5. 5
      Xenon
      134
  • Tell a friend

    Love Neowin? Tell a friend!