Auto Pruning An Sql Database With Php - How can i do this?

Hey all.

Now i have a DB, an SQL DB, and i need to auto prune the data there to delete rows lder than a certain time, lets say 2 months for now, the question is how do i do this?

The obvious thing is just to add a field which is the numerical representation of the month when the data is entered and every time the DB is accessed it will check this number against the current month,in a numerical format, and if the difference is two or greater that row is deleted, so if the month is January, it would be O1 and if the current month is march then it would be O3 and the difference is 2 so it would be deleted. This sounds fine untill you take into account how months work... it goes from 1-12 then back to one again! i guess i can overcome that but it seems complicated.

Now im thinking perhaps SQL has a built in way of comparing dates? if so what is it?!

I could simply delete any entries after a certain number of rows but if this website becomes popular then this number of rows might be exceeded in a few days or less so even recent entries would be deleted.

Any ideas?

Thanks

 

 

 

Comment/Reply (w/o sign-up)

Use your idea of adding a field with the month, but just keep incrementing the months as the years go by. For example, January would be one (or whatever month you choose to start on), Februrary would be two, but January of next year would 13. Its not as hard as it sounds. You can find out what month it is based on this equation.

Let y = year you started
Let m = current month
Let z = current year

12(z - y) + m

Does this help?

Comment/Reply (w/o sign-up)

Ah yes, that makes sense, so long as i use the year as a four digit number, not a 2 digit because every change of the decade would screw it up!

thanks for that, i shall try that when i get in from college later, it would be interesting to know how Microsoft Access, the DB application, does this because yu can compare dates directly in that and as far as i know it is just a front end application for an SQL database, though i might be wrong.

Comment/Reply (w/o sign-up)

Apologies, I didn't realise that the question has already been answered.

Comment/Reply (w/o sign-up)

Thats ok if you have a different solution then id be interested to hear it, at the moment ive got ghostrider's solution in the works but im still interested at different angles to approaching the problem for future reference, and it might help someone else out too


By the way Ghostrider, thanks, it seems to be working which is good!

Comment/Reply (w/o sign-up)

Simple Solutions
Auto Pruning An Sql Database With Php

 mysql has date functions that work great for this.

set a column for the current date and call it with now() or update on change.

Next you can delete the row with.

DELETE FROM `table` WHERE `date` < DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 MONTH)

 

-reply by jcbones

Comment/Reply (w/o sign-up)