Auto Delete data in MySQL

deleting mysql data by elapsed time...

[RottNKorpse]

I have found something simular to what I am wanting to do on this page... [webmasterworld.com...] However it is not exactly what I am wanting. That code is set up so that after 24 hours it deletes the data from the table. What I want for my function to be is to automatically delete the data after 1 hour. I am sure it will be used often and I dont want it to keep the data for very long.

So could anyone tell me how I would go about doing this.

[StupidScript]

Welcome aboard, RottNKorpse.

Using the same technique described by dreamcatcher in the thread you mention, you would set up a

cronjob

for a PHP script that ran every hour or whenever you want it to run.

You need the field that holds the "birth time" in the database as a Unix timestamp, then your PHP script would check for and delete and db rows that met your criteria ( birthtime > now() - 1 hour ).

What flavor of server are you running?

[RottNKorpse]

my server is on Linux Red Hat...I not really sure how to make a cronjob but do I use the same type of thing such as the date format or is there a time format I should use. Btw thanks alot for your help.

Edit: Also would I have to run something to make it delete because I was wanting it to be 100% automatic.

[StupidScript]

For info on running a cronjob, see the man page (

man 5 crontab

), or perhaps this is more convenient [rt.com]. The general idea is you take advantage of a little daemon that checks every minute for something to do ... in your case, you'll tell it to run a PHP script every hour, every day. That script will do the db work. Cron will trigger it.

In your database, you'll need a field of the type

timestamp

, i.e.

bornon timestamp

. Whenever you are inserting a new record, you insert the value

NULL

into that field, and it will include the current timestamp (the number of seconds that has passed since 1970-01-01 00:00:00 GMT) in the field.

You will then use another timestamp (

now()

) to see if the current time is 3600 seconds or more than the bornon value. If so, delete that record.

[StupidScript]

How clear was that?! Yeesh ... I ought to write MS documentation ...

1) Write your PHP script (similar to dreamcatcher's, see below*)

2) Store it somewhere convenient and

chmod 700

the file to give the owner (you) permission to execute it.

3) Cronjobs are typically stored in

/var/spool/cron

on a RH system. They are little text files named for the user (i.e.

root

) that follow the syntax you can see in the man page for crontab, as I noted in my last message.

The cron daemon, which is already running on your server will check

/var/spool/cron

once per minute to see if it has anything to do. It will read your little

root

(or whatever user you are) text file once per minute. When it discovers that it is time to run the script, according to the instructions you wrote in the text file, it will attempt to run the PHP script.

For example, running as root, the cronjob might be:

### set the clock (use your local timeserver)

0 * * * * /usr/sbin/ntpdate -u ntp.dn.net >/dev/null 2>&1

### run your script every hour of every day

0 * * * * /usr/local/scripts/clean_db.php

*4) Your PHP script will do two things: check for timestamp field against

now() - 3600

, and delete any records that match that condition.

For example:

$dbconn=mysql_connect("localhost","user","pass") or die ("No conn: ".mysql_error());

$killtime=(now() - 3600);

$q=mysql_db_query("db","delete from table where bornon < '$killtime'") or die ("No q: ".mysql_error());

That's better! ;)

[RottNKorpse]

lol thanks for the translation...and thanks alot for the help that was exactly what I needed.

Edit: Sorry to ask many questions but I was wondering if you could tell me how I could display data from mysql but starting at a certain row yet let it still change if new rows are added.

That might not make much sense so here is what I am wanting to do.

I am wanting to make a image gallery where it has 3 rows of 3 images on each row (9 images total per page) however when using a loop in PHP I cant have multiple rows well not that I know of. So what I am wanting to do is start the query by descending order yet stop at 3 mysql rows and then move to the next gallery row and start the query again but using the next set of three mysql rows.

Ok I hope that make sense...but I'll clarify if you have any questions on that.

[jatar_k]

easy enough to select 9 rows using an offset to tell it where the first row should be, take a look at LIMIT

[dev.mysql.com...]

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15

[RottNKorpse]

thanks alot for your help...its very refreshing to find a forum that has members who actually respond to questions. I'll be sticking around just incase I can help people. Thanks again.