Delete a mysql row after a number of days

[oskare100]

Hello,
I've a Paypal IPN PHP script that creates an account in one table and then insert the transaction details into another table.

Three days after the account has been created/the payment has been recieved and the details has been inserted into the database I need the users to be able to go to a page and view their username and password by typing in their email and the auction ID (which are in the database).

I thought that I can create a new table where I insert the date, the username, the password and the auction ID when the account is created and then I can have a script that removes that row after three days (because the login and the transaction details are already saved in the other tables). In other words when the row has been deleted they can't get access to their passwords anymore by going to that page and typing in their username and password because the their email and auction ID aren't in that table anymore.

Basicly I need to know at least how to make the delete script delete the rows after three days.

I've asked for help on another forum but I can't get it to work, here is the codes I've (with help) done so far but it doesn work..

This is the PHP code that should add the date:

[code=php:0]//Connect to MySQL
mysql_connect("localhost", "test", "test") or die(mysql_error());

//Select file system database
mysql_select_db("test") or die(mysql_error());

$v = time();
mysql_query("INSERT INTO temp_users (invoice, receiver_email, item_name, item_number, quantity, payment_status, pending_reason, payment_date, mc_gross, mc_fee, tax, mc_currency, txn_id, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_status, payment_type, notify_version, verify_sign, referrer_id, memo, for_auction, auction_buyer_id, auction_closing_date, auction_multi_item, account_username, account_password, `account_group`, account_email, date) VALUES('$invoice', '$receiver_email', '$item_name', '$item_number', '$quantity', '$payment_status', '$pending_reason', '$payment_date', '$mc_gross', '$mc_fee', '$tax', '$mc_currency', '$txn_id', '$txn_type', '$first_name', '$last_name', '$address_street', '$address_city', '$address_state', '$address_zip', '$address_country', '$address_status', '$payer_email', '$payer_status', '$payment_type', '$notify_version', '$verify_sign', '$referrer_id', '$memo', '$for_auction', '$auction_buyer_id', '$auction_closing_date', '$auction_multi_item', '$payer_email', '$password', '$group_id', '$payer_email', {$v}) ")
or die(mysql_error());[/code]

And it does add something like 1163786812

Then this script is supposed to remove the row ( I guess that that is "remove all rows with a date older than 3 seconds"):

[code=php:0]//Connect to MySQL
mysql_connect("localhost", "test", "test") or die(mysql_error());

//Select file system database
mysql_select_db("test") or die(mysql_error());

$v = mktime(date("H"), date("i"), date("s") - 3, date("m"), date("d"), date("Y"));
$sql = "delete from temp_users where date < {$v}";  [/code]

But is doesn't. What do you think is wrong?

Best Regards
Oskar R

[esukf]

Minus 3 days rather than 3 seconds
[code]
$v = mktime(date("H"), date("i"), date("s"), date("m"), date("d") - 3, date("Y"));
[/code]

[oskare100]

Hello,
That was a test, I wanted it to be removed if I runned the script after more than 3 second to see if the row was removed but it wasn't.

Best Regards
Oskar R

[taith]

[code]
$date=date(z)-3;
mysql_query("DELETE FROM example WHERE date<'$date1'");
[/code]
how bout this?

[oskare100]

Hello,
Is that seconds or days?
How do I do it in seconds and days? And what do I write when I insert the date into the database?

Best Regards
Oskar R

[taith]

date(z) is the number of days since jan 1 , somewhere between 1-365. date(z) also compensates for leap years...

just put a row into your mysql for "date", and have it fill in date(z)

coming to think of it... if you go this way... you'd also want
[code]
$date=date(z)+1;
mysql_query("DELETE FROM example WHERE date>'$date1'");
[/code]

so it'd go around newyears...

[alpine]

I would suggest to insert the timestamp simply as mysql NOW() and run a simple delete query using date_sub() like this example:
[code]

<?php

$insert = mysql_query("INSERT INTO table ('time_added', 'bla_bla') values (NOW(), '$bla_bla')");

$delete = mysql_query("DELETE FROM table WHERE time_added < date_sub(now(), interval 3 second)");

?>

[/code]

From http://dev.mysql.com/doc/refman/5.1/en/create-event.html

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

[oskare100]

Hello,
I didn't understand how to get that working, I'm not good at neither PHP nor SQL but I need to get this working somehow.. The other parts of the script are already done and working..

According to the person I spoke to I could use this to insert the data (this is the insert script I've used all the time):

[code=php:0]<?php
$receiver_email = "osakre2020@hotmail.com";

//Connect to MySQL
mysql_connect("localhost", "test", "test") or die(mysql_error());

//Select file system database
mysql_select_db("test") or die(mysql_error());

$v = time();
mysql_query("INSERT INTO temp_users (invoice, receiver_email, item_name, item_number, quantity, payment_status, pending_reason, payment_date, mc_gross, mc_fee, tax, mc_currency, txn_id, txn_type, first_name, last_name, address_street, address_city, address_state, address_zip, address_country, address_status, payer_email, payer_status, payment_type, notify_version, verify_sign, referrer_id, memo, for_auction, auction_buyer_id, auction_closing_date, auction_multi_item, account_username, account_password, `account_group`, account_email, date) VALUES('$invoice', '$receiver_email', '$item_name', '$item_number', '$quantity', '$payment_status', '$pending_reason', '$payment_date', '$mc_gross', '$mc_fee', '$tax', '$mc_currency', '$txn_id', '$txn_type', '$first_name', '$last_name', '$address_street', '$address_city', '$address_state', '$address_zip', '$address_country', '$address_status', '$payer_email', '$payer_status', '$payment_type', '$notify_version', '$verify_sign', '$referrer_id', '$memo', '$for_auction', '$auction_buyer_id', '$auction_closing_date', '$auction_multi_item', '$payer_email', '$password', '$group_id', '$payer_email', {$v}) ")
or die(mysql_error());

?>[/code]

And then I could use this code to delete the row after a number of seconds... I wrote 10 seconds to be able to test the script:

[code=php:0]<?php
mysql_connect("localhost", "test", "test") or die(mysql_error());

mysql_select_db("test") or die(mysql_error());

$sql = mysql_query("SELECT * FROM temp_users"); 
$checkdate = mysql_fetch_array($sql);

do{

$deleteday = $checkdate['date'] + 10; // 86400 is one day in seconds, 60*60*24

if(time() <= $deleteday)
{
$deleteSQL = "DELETE FROM temp_users WHERE invoice =" . $checkdate['invoice'];
mysql_query($deleteSQL, "test") or die(mysql_error());
}

}while($checkdate = mysql_fetch_array($sql)); 
?>[/code]

But it didn't work. Here is a dumb of my MySQL table and as you can see, the row is still there:

[CODE]--
-- Struktur för tabell `temp_users`
--

CREATE TABLE `temp_users` (
  `invoice` int(10) unsigned NOT NULL auto_increment,
  `receiver_email` varchar(60) default NULL,
  `item_name` varchar(100) default NULL,
  `item_number` varchar(10) default NULL,
  `quantity` varchar(6) default NULL,
  `payment_status` varchar(10) default NULL,
  `pending_reason` varchar(10) default NULL,
  `payment_date` varchar(20) default NULL,
  `mc_gross` varchar(20) default NULL,
  `mc_fee` varchar(20) default NULL,
  `tax` varchar(20) default NULL,
  `mc_currency` char(3) default NULL,
  `txn_id` varchar(20) default NULL,
  `txn_id_refund` varchar(17) default NULL,
  `txn_type` varchar(10) default NULL,
  `first_name` varchar(30) default NULL,
  `last_name` varchar(40) default NULL,
  `address_street` varchar(50) default NULL,
  `address_city` varchar(30) default NULL,
  `address_state` varchar(30) default NULL,
  `address_zip` varchar(20) default NULL,
  `address_country` varchar(30) default NULL,
  `address_status` varchar(10) default NULL,
  `payer_email` varchar(60) default NULL,
  `payer_status` varchar(10) default NULL,
  `payment_type` varchar(10) default NULL,
  `notify_version` varchar(10) default NULL,
  `verify_sign` varchar(10) default NULL,
  `referrer_id` varchar(10) default NULL,
  `memo` varchar(255) default NULL,
  `for_auction` varchar(20) default NULL,
  `auction_buyer_id` varchar(64) default NULL,
  `auction_closing_date` varchar(20) default NULL,
  `auction_multi_item` varchar(20) default NULL,
  `account_username` varchar(100) default NULL,
  `account_password` varchar(20) default NULL,
  `account_email` varchar(100) default NULL,
  `account_group` varchar(20) default NULL,
  `date` varchar(20) default NULL,
  PRIMARY KEY  (`invoice`),
  KEY `txn_id` (`txn_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=726856 ;

--
-- Data i tabell `temp_users`
--

INSERT INTO `temp_users` VALUES (726855, 'osakre2020@hotmail.com', '', '', '', '', '', '', '', '', '', '', '', NULL, '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '1163786812');[/CODE]

I'e also tried  a few other things also but I can't get it working.. So please, I need help..

Best Regards
Oskar R