Delete query

[graham23s]

Hi Guys,

 

i have a vasic query that grabs the date orders have been added to cart, but some shoppers add to cart then don't purchase, so i was going to implement a basic query to delete orders older than 24 hours.

 

code:

 

<?php
  $qD = "SELECT * FROM `fcp_orders`";
  $rD = mysql_query($qD) or die (mysql_error());
  $aD = mysql_fetch_array($rD) or die (mysql_error());
  
  // DATE ADDED TO CART
  $dateAdded = $aD['date'];

  // DELETE ORDER IN CART OLDER THAN 24 HOURS
?>

 

This code grabs the dat for me in format: 2008-11-20 10:05:23

 

its the delete query i can't seem to get! when the dates are pulled from mysql if there are orders still there older than 24 hours then delete them!

 

any help would be appreciated!

 

cheers guys

 

Graham

[ngreenwood6]

I would change the database to store the date as a time stamp int(11). Then when you pull it you can just compare the 2 like this:

 

$time_now = time(); //this is how you would store the time it will give you something like 12345678 (seconds since 1900 something) irrelevant

//then you can get 24 hours ago on the page you want the delete query
$time = time() - (60 * 60 *24);

//then make the delete query
$query = "DELETE FROM table WHERE field >= '$time'"

 

You obviously would need to modify this to fit your needs but that is a start. if you run into problems come back and look for somemore help.

[MatthewJ]

There is no need to change the way it is stored to work with it as a timestamp...

 

you can select a datetime column as a unix time stamp like this

 

SELECT unix_timestamp(yourdatetimecolumn) AS whatever FROM table...

 

and then work with it that way

[ngreenwood6]

that is a new one for me. I will have to remember that for the future good work MatthewJ

[Jabop]

Or you can just use intervals.

 

DELETE FROM table WHERE date < DATE_SUB(NOW(), 1 DAY);

[graham23s]

Nice one, thanks guys a few ways to go

 

Graham

[graham23s]

ok nearly there lol

 

would this seem right:

 

<?php
  $qD = "SELECT unix_timestamp(date) AS `UNIXTIME` FROM `fcp_orders`";
  $qD = "SELECT * FROM `fcp_orders`";
  $rD = mysql_query($qD) or die (mysql_error());
  
  while ($aD = mysql_fetch_array($rD))
  {
  
  // DATE ADDED TO CART
  $dateAdded      = $aD['UNIXTIME'];
  $date24HoursAgo = (60 * 60 *24);
  
  // DELETE ORDERS IN CART OLDER THAN 24 HOURS
  $deleteQuery = mysql_query("DELETE FROM `fcp_orders` WHERE `date` > '$date24HoursAgo'");
  
  }
?>

 

Thanks guys

 

Graham