Page 1 of 1
Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 12:56 pm
by sleepydad
I have created a site where people sign up for a weekly event. I would like to know how to have PHP truncate the table the day after the event. If someone signs up for Saturday, October 31, I want the table to be cleared on Sunday, November 1. You get the jist. Any prefab script or tutorial reference(s) would be greatly appreciated.
Thanks -
sleepydad
Re: Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 1:19 pm
by califdon
I wouldn't even do that. You won't achieve anything useful by destroying data. Unless you're expecting millions of signups per day, just leave them there! Each record should have a date for which they signed up. Some day you might just find it useful to see how many people attended last year, or by month, or how many times the same person signed up, etc. etc. Disk storage is cheap cheap cheap and performance won't be affected in the slightest unless you have many millions of records.
Re: Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 1:38 pm
by sleepydad
I understand what you're saying, but I have one display on the site to show the people signed up for a given day. This same display needs to be cleared each week prior to the following week to show the current 'enrollees'. Perhaps if you see what I'm talking about, you'll get a better grasp. The site is:
http://www.kasebergkronies.com
The tab in question is the third one in from the left at the bottom labeled 'who's playing'. Each week, my buddies and I get together to play volleyball. Using the 'players' option at the top adds your name to the list in the who's playing tab. Say for example that we get a game together for Thursday at 4. We may play again on Saturday or Sunday, so I'd like to truncate the table Friday to clear out the Thursday players and have a clean slate for the new sign ups.
Make sense?
Thanks again!
Re: Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 2:38 pm
by califdon
Not really. But that's a very good looking site. I'm afraid you don't actually understand me. My comments have nothing whatsoever to do with how you use the data, they relate to how the data are stored, which is my particular area of expertise. No way could I design a classy site like that, but I do know a bit about databases. Store everything. Always. Never destroy it. (That's exaggeration, to make my point.) That's how databases work. Use queries to extract the data appropriate to using it for some purpose. Think in terms of storing the data forever (certainly there are practical limits, but think of it this way and you will never regret it). Clumsy efforts to delete data will yield nothing but a headache, trust me. On any particular day, just display the data for the day or days ahead, as you choose, based on the dates.
Re: Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 4:46 pm
by sleepydad
Thanks for the compliment(s) on the site -- much appreciated. I gotcha' on the need to archive, but honestly have no use to archive this particular data. Thursday night after the sun goes down and we're icing our aching joints and basking in Advil the info from the previous week is not now nor will it be useful or need to be retrieved. Game's over and we're licking our wounds pondering next week. I absolutely see the need for backing up your data/database in many (if not most) instances, though. Your point is well taken on that, and I practice that on a great number of my other sites.
I'll keep this post live and see if I can get any other input. Thanks again for your time!
Re: Truncate MySQL table the day after
Posted: Sat Oct 31, 2009 5:27 pm
by califdon
You're still making a strategic mistake, but it's your site and your project. Good luck.
Re: Truncate MySQL table the day after
Posted: Sun Nov 01, 2009 1:50 am
by John Cartwright
I completely agree with the above points, however, if you insist on pursuing this, you could do something like:
Code: Select all
DELETE FROM `mytable` WHERE WEEK(created) <> WEEK(NOW())
which will delete any row not within the current week (specified in mysql)
Re: Truncate MySQL table the day after
Posted: Sun Nov 01, 2009 10:52 am
by Bill H
I once had a client who complained the the historical list was getting too lengthy and wanted me to provide a deletion feature to get rid of data more than one year in the past. I said let's just alter the display to only show data for the past year and keep the old data, and she said oh hell no, delete it. After some back and forth she agreed to my way and I changed the display to only show one-year-old data.
A couple years later she came back and wanted to know if we could add a feature to let her choose to look at the older data as an option. Seems she needed to check something on an older record.
I said sure thing and changed the viewing procedure so that she could select the year to view. She was really grateful that I had talked her out of deleting the old data.
At some point you are going to want to know "Who participated in week of Apr 8th?" and are going to regret that you deleted that data.
Re: Truncate MySQL table the day after
Posted: Sun Nov 01, 2009 11:33 am
by sleepydad
Thanks again all. Honestly, in one year or a hundred years I won't care who I played volleyball with on Thursday night back in 2009 nor will anyone else. All I'm capturing in this site is a name, a day and a time for a group of guys to get together, drink beer and play volleyball. It's a sign up sheet. Nothing more. Nothing less. As I stated in my previous reply, I do archive data on other sites that I've created and actually do have other MySQL going on in this site that is archived but there is absolutely zero reason to do it for the sign up sheet...none. There are twelve or so guys in my group. If you wanna' play, you sign on to the site and say so. If you don't, you don't. Next week we'll do the same thing. And the week after, and ...
Currently, if we play on Thursday night I go in through a simple PHP script that I wrote and 'truncate `table`' Friday morning to clear the display for the next time we decide to play. I want this process to be automated so I don't have to remember to do it.
Re: Truncate MySQL table the day after
Posted: Sun Nov 01, 2009 1:51 pm
by Bill H
Well, if avoiding writinf a "DATE=???" SQL statement is all that valuable to you, you probably want to do a Google on "cron job."