Best way to expire records 14 days old

celere
celere used Ask the Experts™
on
Hi Experts!

I've a mysql database of classified ads. The database itself contains a unique account number, and the (text) ads are pulled into php via the <?php virtual ?> tags, where the ad is accountnumber.htm. The body of the ad does not occur in any database field.

I have a perl script that will delete the old .htm files, but I don't know the best date format for deleting the mysql records. I understand there are a few different formats and/or SELECT calls I can make (right?).

I have date_opened and date_expires fields, but I haven't set them to anything because I don't know the best way to implement them.

It need not be automatic. For now, I would rather have a perl script where I delete the ads with a click of a button.

So, I am looking for (a)optimum date formats that perl can sort through, and (b)please? a code snipped to show me how perl will filter these date formats.

Your suggestions are, as always, greatly appreciated. Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hello

Althought I never use directly PERL, I'll try to help you.

I guess you know how to delete files using the atime() or mtime() functions of your platform (filesystem functions, date & time of files for creation, access or modification). I guess the "creation time" ctime() is enough for you because once the ad HTM file is created, it won't be modified afterwards.

What I would suggest is :
first to make a one-shot program in whatever language you like, searching your repository directory for ad files, getting their creation date & time and pushing this value in your database using the format DATETIME which is compatible with string-format YYYY-MM-DD HH:MM:SS
Also put a standard expiration date, sema format, for all the newly created records.

The first pass is done.

Now you'll have to make the program which deletes old ads. It's easy. First build a "DATETIME compatible" string variable (say, $datenow ) holding the current date and time, and perform someting like this (in PHP) :

// here get your_link_ID, connect to database, etc
$linkID=mysql_connect("$dbHost","$dbLogin","$dbPassword") or die ("bad connect".mysql_error());
mysql_select_db($dbName,$linkID) or die ("bad select db. ".mysql_error());

query="select id,filename from ads_table where expiration_date<'$datenow';";
$result=mysql_query($your_link_ID,$query) or die "cannot perform query $query"; // do it your usual way
while ($res=mysql_fetch_array($result)) {
  // here delete the file $res["filename"];
  // log stuff ?
}

it's done. This script should run every midnight after 00:00:00

put it in your crontab, or as a scheduled task, or leave open a browser window with a refresh of 86400, or use the "RobotOuaibe" robot I did in Delphi that you can find at www.edainworks.com in the free tools area

good luck

Author

Commented:
Hi VGR - Thanks for the response. I *think* I know what you're saying, and will likely spend the entire day confirming that much :)

>I guess you know how to delete files using the atime() or mtime() functions of your platform

Actually, I got help with this script from EE too, and it uses:

$delete_age      = 14;        
...
next unless int (-M _) > $delete_age; # skip if new
unlink $file or die "Can't delete ";

So, no, I am not using system mtime() functions (that I know of)

>make a one-shot program ...pushing this [creation date] value in your database

So, you think I should use a similar script (to deleting old files) to get the creation date, then push that into the database WHERE $filename = $account_no? Right?

Does it matter to MySql if the field is a DATE field or a TEXT field?  It seems to want to put unique values for the creation date, when a DATE field.

I see that SQL has functions like:
SELECT curdate();  -- today, and
SELECT date_add("2000-05-03" INTERVAL 1 DAY); -- increments accordingly

If I can get this to work properly, it seems like I could enter an expiration date at the same time as the creation date, and then delete records where expiration < today. (Only, I'm not sure how to get the above SELECT statements to fill the fields)

But you don't believe I need an expiration field? Just a script that will compare creation date to today's date (as in my file deletion script?).  I'll try it.

Thanks!


Author

Commented:
Alas, I figured it out. You're right. Who needs an expiration date?  Here is what I'm using:

When someone adds to the database:

$sql = "INSERT INTO classifieds SET
     d_created = curdate(),
....
"

And when I want to delete old records:
DELETE FROM classifieds WHERE d_created < date_sub(curdate(), INTERVAL 14 DAY)};

where curdate() produces today's date, and date_sub() subtracts the interval (in this case 14) from current date. If the d_created is less than the result, it is deleted.  Thus eliminating the need for a d_expires field.

I think this should work fine. Does anyone foresee any problems with this?

Thanks again for your help VGR. It wasn't exactly the answer I was looking for, but indeed got me to reevaluate my approach.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

VGR

Commented:
Well, sorry, but I was not available recently.

I ***think*** you "need" an expiration date, if your ads do not have the same "duration" as the others (ads).

If the "duration" (lifetime) of ALL your ads is fixed, then o course you don't need an expiration date. (that is the solution immediately above using cur_date() = now() and checking with INTERVAL 14 DAY


I think you misunderstood me.
I basically said :
1) you currently have a set of files and an empty database (as far as the expiration/creation dates are concerned)
2) perform in one pass the exploration of your repository of ads' files, thus filling in your database's fields "creation_time" (from ctime() of the ad file), and I was ***suggesting*** (for a start) to fill the expiration_date with creation_date + standard expiration delay (15 days)

3) build a robot page that each day checks what I wrote : select * from classifieds where expiration_date<now

ie : the ads that have expired...

don't forget to delete the ads files AND ALSO the records in the DB

thanks

Author

Commented:
Hi VGR:

I'm pretty sure I understood what you meant. But, if I have a directory of files, and I go through and read/push these regularly, won't it be rather time consuming as the directory grows?  Wouldn't I be re-reading some every time? And re-writing the db fields each time?

If not, how is this a better way than having SQL use its own functions to enter the dates?  I'm not arguing, I'm just curious as to why it's preferable.

Some ads will have different expiration days, but they will be in a different table. (paid vs. free, where paid last longer).  For now (while I await to learn what glitches may arise) I have separate perl subroutines for this. One sub will go through the "free" directory and delete 14 day old ads, then the records from the "free" data table. Another sub will go through "premium" ads (separate directory, separate table).

I'm not sure how to build a robot page. Could you point me to a tutorial?

Thanks again.
VGR

Commented:
the suggested file-searching is only to be used the VERY FIRST time, to populate your database' fields that were left empty while they should not have been ;-).

Afterwards, it's up to your upload pages to fill properly the creation_date and expiration_date in the DB

As for the robot, I sure have some of them 8-)

In fact, you build a PHP page that performs the cleanup, but you don't call it directly (it would require you to stay until midnight before the keyboard, and this every day ;-). In stead, you crontab-ise, Schedule it or use my free RobotOuaibe tool, who is basically :
- a timer for polling
- checking that the scheduled time has not come
- if it has, then call the web page as if it were a browser

No big deal 8-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial