Jump to content
xisto Community
Sign in to follow this  
shadowx

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

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 startedLet m = current monthLet z = current year12(z - y) + mDoes this help?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites
Simple SolutionsAuto 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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×
×
  • Create New...

Important Information

Terms of Use | Privacy Policy | Guidelines | We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.