6 replies to this topic

[piskie]

I have an accommodation availability and tariff page that is populated from a MYSQL table and would like to incorporate Automatic Pruning by date expiry from the table. What is the best way to accomplish this using PHP and MYSQL and my just about intermediate coding skills.

Thanks in advance for any help and guidance.

[chrishirst]

DELETE FROM table WHERE datecolumn BETWEEN date1 AND date2 ;


or set/reset a flag to deactivate the entries.

[piskie]

Chris, I would like to delete a date entry when the actual date passes the entered date.
Example: the entry for week begining Saturday 7th June should be automatically deleted when the actual date reaches the 8th June.

[Randy]

Delete the whole database table entry or just the date field?

Either way you could do it with a simple MySQL delete statement as Chris laid out above, then set up a little cron job on a script to parse through the database table to figure out what needs to be deleted and what doesn't. Set up the server to fire it at 1am or something and you'd always be good to go without any daily hands on action.

[piskie]

Thanks Randy and Chris, I will set about doing that in the next few days.

[Randy]

One small heads up...

Figure out how the date is being set originally before you even dig into the coding aspect. There are different formats (eg php date is not necessarily the same as using a MySQL date function) and knowing what is used to set the date initially will save you a lot of heartburn.

[piskie]

Thanks Randy, I am already on to that as I am trying to convert a Day+Month+Year set of dropdown inputs (Client Interface) into "Unixtime" which I already use to store a "lastupdated" field.