0

I have tables named 20161006_cost and 20161006_area. The tables' name prefix is from the date they were created.

Can they be deleted automatically with a trigger, if the system date is more than 24 hours from the tables date creation? Or maybe with SQL or PHP code.

database mysql
engine innodb

===========================

edited

found the query tho show the table name

$qdrop=mysqli_query($connect,"select TABLE_NAME,create_time
 FROM INFORMATION_SCHEMA.TABLES 
 WHERE table_schema = 'mydb' 
 and CREATE_TIME <='2016-10-07' and TABLE_NAME LIKE '2016%'
");

but i can't use fetch array to show it

while($data=mysqli_fetch_array($qdrop)){
   echo $data['TABLE_NAME'];
}

error code:

Fatal error: Cannot use object of type mysqli_result as array in E:\xampp\htdocs\....
Agung Chrisna Nugroho
  • 39
  • 2
  • 9
  • create an event. Run queries against info_schema. Drop at will. – Drew Oct 06 '16 at 08:12
  • 1
    1 minute read [here](http://stackoverflow.com/a/37901661), more in depth [here](http://stackoverflow.com/a/32508935) . You need to look at `INFORMATION_SCHEMA` and bank on good table naming conventions. The whole thing is risky. Additionally you would typically need a [Cursor](http://dev.mysql.com/doc/refman/5.7/en/cursors.html) – Drew Oct 06 '16 at 08:16
  • Set a cron which will check the time & will drop the table – Dipanwita Kundu Oct 06 '16 at 08:53
  • got it. so first show the date creation schema then drop the table with looping. – Agung Chrisna Nugroho Oct 06 '16 at 08:56
  • Get all the tables name like *_cost, then explode the table name "_". If prefix time < current date then drop the table. Check your mysql user must have table 'drop' privilege. – Dipanwita Kundu Oct 06 '16 at 09:14
  • Why not just have the tables with a timestamp in it? This would be better than creating tables etc. with the prefix – Ed Heal Oct 06 '16 at 09:39
  • @EdHeal becaus mine is not for multi user table. each user has their own table so i have to give the prefix – Agung Chrisna Nugroho Oct 06 '16 at 09:41

0 Answers0