CODEWITHSUNDEEP
mysql
user2946986
user2946986

Reputation: 63

mySQL Clearing a database once a month

I currently have a database that I need to be cleared on the 1st of every month, I would also like it to output this query into a file before doing that:

$con,"SELECT * FROM `Totals` ORDER BY amount desc LIMIT 10"

After that I need all "amounts" reset to 0.

I have root access, but i'm unsure if I should CRON or mySQL events and i'm very new to mySQL so i'm not sure how to make it output that query into a file.

Upvotes: 0

Views: 43

Answers (2)

magic-sudo
magic-sudo

Reputation: 1246

So set CRON to execute your PHP(python etc) file every 1st day of month. In that file execute your SQL queries(first query for saving and then for clearing). By using PHP you can simply create directories and files.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133390

You could use SELECT INTO OUTFILE

SELECT *  INTO OUTFILE '/your_dir/your_filename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM `Totals` ORDER BY amount desc LIMIT 10;

Be careful with the quote for inside a PHP string

see musql doc http://dev.mysql.com/doc/refman/5.7/en/select-into.html

Upvotes: 1

Related Questions