Auto-Delete(perl/mysql)

[rugrat15834]

Hi. I need to know how to make a row in the database delete itself after so many days. I did this in another script once and got it to work but this one is really different(actually more basic) So far I came up with:

$sth=$dbh->prepare("select *, TO_DAYS(CURRENT_DATE)-TO_DAYS(stop_date) as expire from prayer_board where stop_date <=DATE_ADD(stop_date, interval, 72 hour)");

stop_date is the name of a column which is a timestamp and if I type out
print"$ref->{'expire'}"; the result is how many days it has been since the row was created. Somehow I need to be able to make rows delete after, say, 30 days. Any, all help would be really greatly appreciated.
Have a great day
Dave :)

[Nedals]

Assuming stop_date is when the row is to be deleted...

$dbh->do("DELETE FROM prayer_board WHERE stop_date<NOW()");

[rugrat15834]

Hi Nedals, Thanx for the reply, however the fix didn't work. I'm really new at this mysql and have done really good(in my opinion anyway), but there is so much I need to learn. In the statement the column stop_date was merely a timestamp which is probably the source of infection here. I've been messing with different ways of using adddate and all kind of things to no avail but I'm thinking that what I need to know is how to do a column like you talked of, that is, how do I , say on April 15 a row is created make it go away on May 15. Does it have something to do with the insert or select statement as well as the delete statement. Or, how do I make the column stop_date to read the date I want it to delete.? I hope I'm not sounding confusing(I'm confused myself, at this point) and I really appreciate the help. Dave

[Nedals]

The line of code I gave you should work; based on the definition you gave.

An insert might look like this:

my $stop_date = <a date when you want to delete the row>;
my $sql = "INSERT INTO prayer_board (<list of columns>) VALUES (<list of variables>)";
$dbh->do($sql);

Set the 'stop_date' column to a 'datetime' type, not 'timestamp'.
timestamp will be changed every time you update the row.

I recomend that you ALWAYS include the column names in your SQL statement, including SELECT's

my $sql = "SELECT <list of desired columns> FROM table WHERE .....

That way, if you ever change (ALTER) your table, you will not have to go back and fix scripts that refer to that table.

If you do a lot of sorting, ORDER BY stop_date, on this table, you might want to consider setting the column type to INT and use the UNIX-TIMESTAMP, FROM_UNIXTIME. That way you will get improved performance because strings are relatively slow to sort. (unless you index the column)

Hope that helps....

[rugrat15834]

I'm following what you're saying and have come to the conclusion that I simply put don't know the syntax for setting the stop_date to the date I would like it to stop. The rest I have though. How hard is it to "pre-set" a date for the stop_date? You have been much much help and I appreciate it more than you could imagine--the best part is I have all but this part of the db working and have put it all together from a book I bought on the subject. Just can't seem to get that one part Would it be okay to beg for the secret to setting the expiration date-they follow no certain pattern, that is, the 30 days could begin at any time or day. Thanx millions, Dave One thing is for sure though, I will be working on this till I get it. I actually like this database ticket.:) :)

[Nedals]

...expiration date-they follow no certain pattern, that is, the 30 days could begin at any time...That does not make a lot of sense.
'...begin at any time??' How about begin on 01/01/2032.

I think you mean 30 days after it was originally posted.
In your perl script..
my $startdate = time; ## gets the date/time of entry. (in secs from the epoch)
my $enddate = $startdate+(60*60*24*30); # add 30 days

In your sql...
.... stop_date=FROM_UNIXTIME($enddate) ....

[rugrat15834]

Okay, first off, I apologize for coming off sounding pretty lame but sql is very new and strange to me at the moment. ( I know the feeling of trying to deal with people who don't have much of a clue to begin with and it didn't really make much sense( I was just getting back from a therapy session at the hospital and was a bit "groggy" but besides all that, what I meant by could begin at any time was just the 24/7 thing. You are right when you said about it being 30 days after the original post Basically what I'm looking for is a snippet containing the info needed to make a row remove itself after 30 days of being live. Once I get through this one(this section is the icing on the cake for this script) I've decided to sign up for a mini course on mysql to keep me from looking like half an idiot at times--of course a newly acquired neurological problem is doing all it can to mess with me at the same time( not that I'm usually a wizard or something---definitly not, but your help is appreciated more than you could imagine through a really troubled time. I am going to continue the pursuit though, working with the epoch might be the answer, Have a great day, Dave

[Nedals]

Okay, first off, I apologize for coming off sounding pretty lame
Everybody has to start somewhere. :)

If you can, get MySql by Paul Dubois. ( $50.00 or so :( )
It has good discussions on the basics and using mySql with Perl

[rugrat15834]

HI, The book by Mr. DuBois is the one I've been working(attempting) with. It is a very fact filled informative book but I guess I'm just not that far along yet but the "experiments" will continue. Rudeness???? I understand where you're coming from-I used to teach guitar and know how frustrating it can be. If I hadn't got sick and half crippled up I'd still be doing it but I'm working on getting some basics down before actually paying for a course dealing with perl/mysql. It probably won't be so hard in a (even online)classroom type atmosphere. Still, I thank you for trying to set me straight. Take care, God bless and have a great day->one day I'll be able to look back at this experience and get a laugh out of it--Dave