MYSQL Question

I have a question. I am still trying to understand what MQSQL is all about. I currently have a table with 3 columns and values in them. Is there a way to set it so that these values are purged every 24 hours automatically on the server?

Nathan

You'll want a Cron-Job for something like that. A Cron-Job will set code to run every x minutes/hours/days/weeks/etc.

As far as I know, there's now way to purge a MySQL-Table automatically.

Depending on what you wanna do, it's not a good idea to do this anyway. If you want the data to be stored for 24 hours, you might loose the most recent data as well when you purge the table.

I'd add another column to your table where you store the date_time when the data is saved.

Depending on how much control you have on your server, you could do either one of the following:

Every time you store some data, you call a routine that checks for entries which are older than 24 hours. Depending on how many write-accesses you have to this table, this might result in a pretty high table-access-rate.

Or...

You write a script that checks for entries that are older than 24 hours and deletes them.
Run this script every 24 hours using a scheduled task (cronjob) on your server. BUT: if you're writing the script, let's say in PHP, you can't just call the script running a scheduled event, since the server wouldn't interpret the PHP-Code. Instead, you have to run it through WGet - which calls the script like a browser and thus, the script will be executed).

Both ways can be simply made by a SQL-Statement, that checks the stored date_time to the actual date_time.

should look something like this:

WHERE `col_datetime` < NOW() - INTERVAL 1 DAY

(this is not tested though

HTH...

Awesome replaies, thank you. I will look into this as a pointer for what I am trying to get accomplished!

Hi Nathan,

You can both empty and drop a MySQL table automatically but, as the others have said, it's a dangerous road to take. You might want to consider using a database written on-the-fly to a text file held in the root directory of your interface page, instead of within your MySQL database. It's easier to manipulate that way.

I have a demo guestbook on one of my sites: anyone can write to it but, as this is a demo, the data is wiped every couple of hours using a php file run as a Cron job.

Here's the Cron directive (you'll need to check the exact format your host uses):

php $HOME/public_html/sitename/foldername/clearguests.php >>$HOME/cron.log 2>>$HOME/cron.err

As you see, my hosts' setup does permit you to run a php file as a Cron job so, if yours does, you could follow H.W.'s hints on identifying the records to be wiped then run your "wipem.php" every 24 hours.

If you only have 3 fields, though, I would go for a text database. Safer!
Just my two-penn'orth.

Cherry.

Thank Cherry (and all)...

I have been tinkering all weekend over this. It sux! I know what I want, and I have a lot of creative ideas but I am lacking the development (scripting) skills. This is the worst punishment ever!!! LOL

Thanks again,
Nate

Nah...it's the BEST MOTIVATION ever. Think of it as an opportunity to broaden your horizons with the added bonus of a goal to accomplish

OK, we've all been there Some of us are there are on a regular basis, ahem!

Can you outline what you want to achieve?

How crucial is the 24-hour data? Is anybody likely to hack it in the 24 hours?
If you need to perform functions on the data submitted to your table, mebbe you do need to use your MySQL dbase.

I realise this isn't a MySQL forum but am pretty damn sure you'll get helpful pointers here, for your task in hand.

If it's just an unfamiliarity thing for you, start here:
http://uk.php.net/mysql

and take look here:
http://www.tizag.com/mysqlTutorial/

Follow links & use search ...

Cx

[edited for excessive smileys ]

PS: Hi, Ken!