A mySQL query to remove old attachments

[waddy]

Hi

Im trying to write a mySQL query to delete attachments older than a certain date..

For example : delete attachments posted before February 1st

Im cannot get it to work , i dont understand the dateline in VBulletin Attachment table

Can anyone write a working SQL query for me to use, i think alot of people will get use from it


thanks

[RINCE]

The dateline field contains a unix timestamp.

[waddy]

Hmmm sorry but thats as clear as mud :)


Im not a Unix guy or a SQL programmer, I just need help with a query to reduce my Database by 40MB before it dies

[Pogo]

select * from attachment where dateline < UNIX_TIMESTAMP('yyyy-mm-dd hh:mm:ss')
This way it will show you all attachments before the date you enter.
Change select to delete and you can delete them.

[waddy]

Thanks Pogo ... :)

Thats excellent ..... i appreciate your help ....

[waddy]

It Doesnt seem to like that SQL statement

---------------------------------------------

Error
SQL-query :

delete * from attachment where dateline < UNIX_TIMESTAMP('2002-01-15 02:00:00')

MySQL said:


You have an error in your SQL syntax near '* from attachment where dateline < UNIX_TIMESTAMP('2002-01-15 02:00:00')

-------------------------------------------------

IM using phpMyAdmin 2.2.1

[Pogo]

My fault :(

delete from attachment where dateline < UNIX_TIMESTAMP('2002-01-15 02:00:00')

[waddy]

No Pogo , my fault for being Dumb :(

i was just reading about it but was worried to use it , "just in case"

Thanks very much

Waddy

Note: I have 1050 people waiting for activation, obviously they have used a fake email ... is it safe to EMPTY the whole lot in the table

"useractivation"