How to empty Table in MySQL database Daily with cronjobs ?

Lets say the database name DatabaseName1 and the Table Name TableName01 how we could empty this table with cronjob daily?

 

Hi!

you can create a php script.

truncatetable.php
Example:
<?
$db_host = "localhost";
$db_user = "username";
$db_pass = "password";
$db_name = "databasename";
$connect = @mysql_connect($db_host,$db_user,$db_pass);
@mysql_select_db($db_name);

// Empty table
$query = "TRUNCATE TABLE TableName01";
mysql_query($query);
?>

Then setup the cronjob
Example:
http://www.monetizers.com/cronjob.php

0 0 * * * wget http://www.yourdomain/truncatetable.php


More information about wget http://www.gnu.org/software/wget/manual/wget.html

Best,
Jakomo

 

TRUNCATE TABLE command reset the auto_increment to 0. If you don't want to reset this, then simply use DELETE command.

 

jakomo, thanks
mwasif, I want to empty not delete table structure. only records...

 

Hi dupehost,

Your welcome, myasif say delete and truncate are different, for example if you have a column with auto increment with truncate it will setup to 0 and with delete the auto increment will continue with the last id number.

With Delete you will not "delete" the table, to delete the table the sentence or command is DROP TABLE

More information about the diference
http://www.sunilb.com/mysql/difference-between-mysql-delete-and-truncate-table

Best,
Jakomo

 

Thanks for explaining jakomo.

 

jakomo, Thanks, How about optimizing all tables how i can add it ?

 

This can be easily done via a Shell Scripts.
You schedule a cron job to run the shell scripts daily.
Inside the shell script, you login into mysql and clean up the tables.

 

good idea to optimize all tables for sites with alot of traffic also- ie..new users and so on

 

just make a sibmple executable file
/usr/local/bin/mysql dbname < 'delete from table tablename;'

note, path is for freebsd

 

It also can be run like this in cron :

php -q /home/username/public_html/cronjobs.php

Code (markup):